What is the difference between a recursive and non-recursive CTE?

A non-recursive CTE defines a simple temporary result set, while a recursive CTE references itself to perform repeated operations, typically to traverse hierarchical data.

Can recursive CTEs handle cycles in data?

Recursive CTEs do not automatically detect cycles; if cycles exist, they can cause infinite recursion. You must design queries or data constraints to prevent cycles.

How do I improve performance of hierarchical queries using CTEs?

Index the columns used in joins, limit recursion depth with MAXRECURSION, and avoid unnecessary computations inside the recursive member.

Is there a maximum recursion depth in SQL Server?

Yes, by default SQL Server limits recursion to 100 levels, but this can be changed using the MAXRECURSION query hint.

RPATechnology.in

Hierarchical Queries with Common Table Expressions (CTE) in SQL Server - Tutorial | RPATechnology.in

Topic 13718 min readStructured Tutorial

Common Table Expressions (CTE) for Hierarchical Queries in SQL Server

Quick Answer

In SQL Server, Common Table Expressions (CTEs) enable hierarchical queries by using recursive CTEs. This allows you to query data with parent-child relationships, such as organizational charts or category trees, by recursively joining rows until the hierarchy is fully traversed.

Learning Objectives

Understand what Common Table Expressions (CTEs) are in SQL Server.
Learn how to write recursive CTEs to perform hierarchical queries.
Apply recursive CTEs to real-world parent-child data structures.

Introduction

Hierarchical data structures are common in databases, representing relationships like employees and managers or product categories and subcategories.

SQL Server's Common Table Expressions (CTEs) offer a powerful way to query such hierarchical data using recursion.

This tutorial explains how to write hierarchical queries using recursive CTEs, with practical examples and tips.

Course Progress0%

Was this topic helpful?

Your feedback helps us improve this lesson.

Topic information

Author

RPATechnology Team

Last Updated

June 2026

Suggested learning path

Related Courses

Continue learning with related technologies and tracks.

Programming LanguagesBeginner FriendlyCareer Focused

C# Programming Course for Beginners to Advanced | Learn C# and .NET

Master C# programming with a complete beginner-to-advanced course covering OOP, collections, LINQ, async programming, Entity Framework Core, ASP.NET Core, Web APIs, design patterns, and real-world projects.

Structured lessons

View Course

Understanding Recursive CTEs for Hierarchical Queries

Recursive CTEs allow a query to repeatedly execute and combine results until a termination condition is met.

They are ideal for querying hierarchical data where rows reference other rows in a parent-child relationship.

Anchor member: The initial query that selects the root rows.
Recursive member: The query that joins the CTE to the base table to find child rows.
Termination: When no more child rows are found, recursion stops.

Basic Structure of a Recursive CTE

A recursive CTE consists of two parts separated by UNION ALL: the anchor member and the recursive member.

The anchor member selects the starting point(s) of the hierarchy.

The recursive member references the CTE itself to find related child rows.

Recursive CTE Structure
Part	Description
Anchor Member	Selects root rows (e.g., top-level managers)
Recursive Member	Joins CTE to base table to find children
Termination	Occurs when recursive member returns no rows

Example: Employee Hierarchy Using Recursive CTE

Consider an Employees table with columns EmployeeID, Name, and ManagerID representing a company's organizational chart.

We want to list all employees under a specific manager, including indirect reports.

Employees Table Sample Data

Here is sample data illustrating the employee-manager relationship.

Employees Table
EmployeeID	Name	ManagerID
1	Alice	NULL
2	Bob	1
3	Charlie	1
4	David	2
5	Eve	2
6	Frank	3

Recursive CTE Query Example

The following query uses a recursive CTE to find all employees reporting to Alice (EmployeeID = 1).

Examples

Recursive CTE for Employee Hierarchy

WITH EmployeeHierarchy AS (
  -- Anchor member: select the manager
  SELECT EmployeeID, Name, ManagerID, 0 AS Level
  FROM Employees
  WHERE EmployeeID = 1
  
  UNION ALL
  
  -- Recursive member: select employees reporting to previous level
  SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1
  FROM Employees e
  INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy ORDER BY Level, EmployeeID;

This query starts with Alice as the root and recursively finds all employees reporting to her, adding a Level column to indicate hierarchy depth.

Learning Objectives

Introduction

Was this topic helpful?

Topic information

Recommended Next Topics

CTE Performance

Examples

Best Practices

Advanced Scenarios

Related Topics

Related Courses

C# Programming Course for Beginners to Advanced | Learn C# and .NET

What is a Common Table Expression (CTE)?

Understanding Recursive CTEs for Hierarchical Queries

Basic Structure of a Recursive CTE

Example: Employee Hierarchy Using Recursive CTE

Employees Table Sample Data

Recursive CTE Query Example

Practical Example

Examples

Best Practices

Common Mistakes

Hands-on Exercise

Write a Recursive CTE for Category Hierarchy

Limit Recursion Depth

Interview Questions

What is a recursive CTE and when would you use it?

How do you prevent infinite recursion in a recursive CTE?

Why use UNION ALL instead of UNION in recursive CTEs?

MCQ Quiz

1. What is the primary purpose of using a recursive Common Table Expression (CTE) in SQL Server?

2. In a recursive CTE, what is the role of the anchor member?

3. Which SQL keyword is used to define a Common Table Expression (CTE) in SQL Server?

4. What is a common mistake to avoid when writing recursive CTEs for hierarchical queries?

5. Given an Employees table with EmployeeID, Name, and ManagerID, how would a recursive CTE help in listing all employees under a specific manager including indirect reports?

Key Takeaways

Frequently Asked Questions

What is the difference between a recursive and non-recursive CTE?

Can recursive CTEs handle cycles in data?

How do I improve performance of hierarchical queries using CTEs?

Is there a maximum recursion depth in SQL Server?

Summary

Troubleshooting

Creating Views

Java Programming Course for Developers and Interview Success | RPATechnology.in

Complete JavaScript Tutorial for Beginners to Advanced | JavaScript Programming Course

Complete MongoDB Tutorial for Beginners to Advanced | MongoDB Course

Complete MySQL Tutorial for Beginners to Advanced | MySQL Database Course

Complete Python Tutorial for Beginners to Advanced | Python Programming Course