Recursive CTE
Learn how Recursive Common Table Expressions work in SQL Server and how they can be used with hierarchical data.
What is a Recursive CTE?
A Recursive CTE is a Common Table Expression that refers to itself.
Recursive CTEs are useful when data has a hierarchical or parent-child relationship.
Common examples include employee-manager relationships, organization structures, category hierarchies and folder structures.
Basic Structure of Recursive CTE
WITH CTE_Name AS
(
-- Anchor Member
SELECT ...
UNION ALL
-- Recursive Member
SELECT ...
FROM CTE_Name
...
)
SELECT *
FROM CTE_Name;
Two Important Parts
1. Anchor Member
The anchor member is the initial query that provides the starting rows for the recursive operation.
2. Recursive Member
The recursive member refers back to the CTE and retrieves the next level of related records.
Example: Employee Hierarchy
Consider an Employees table containing employees and their managers.
EmployeeId | EmployeeName | ManagerId
-----------------------------------------
1 | Amit | NULL
2 | Rahul | 1
3 | Priya | 1
4 | Sneha | 2
5 | Raj | 2
6 | Neha | 3
Here, ManagerId represents the manager of an employee.
Recursive CTE Example
The following query starts with the top-level employee and then finds employees reporting to that employee.
WITH EmployeeHierarchy AS
(
-- Anchor Member
SELECT
EmployeeId,
EmployeeName,
ManagerId,
0 AS Level
FROM Employees
WHERE ManagerId IS NULL
UNION ALL
-- Recursive Member
SELECT
E.EmployeeId,
E.EmployeeName,
E.ManagerId,
EH.Level + 1
FROM Employees E
INNER JOIN EmployeeHierarchy EH
ON E.ManagerId = EH.EmployeeId
)
SELECT
EmployeeId,
EmployeeName,
ManagerId,
Level
FROM EmployeeHierarchy
ORDER BY Level, EmployeeId;
Result
| EmployeeId | EmployeeName | ManagerId | Level |
|---|---|---|---|
| 1 | Amit | NULL | 0 |
| 2 | Rahul | 1 | 1 |
| 3 | Priya | 1 | 1 |
| 4 | Sneha | 2 | 2 |
| 5 | Raj | 2 | 2 |
| 6 | Neha | 3 | 2 |
How Recursive CTE Works
- The anchor member identifies the starting records.
- The recursive member finds records related to the previous level.
- The process continues until no more matching records are found.
- Each recursion can represent another level in the hierarchy.
- The final query returns all generated rows.
Understanding the Level Column
A level column can be added to a recursive CTE to identify the depth of each record in the hierarchy.
0 = Top Level
1 = First Child Level
2 = Second Child Level
3 = Third Child Level
...
Why UNION ALL is Used?
Recursive CTEs commonly use UNION ALL to combine the anchor member and recursive member.
WITH EmployeeHierarchy AS
(
SELECT ...
UNION ALL
SELECT ...
FROM EmployeeHierarchy
)
SELECT *
FROM EmployeeHierarchy;
MAXRECURSION
SQL Server provides the MAXRECURSION query option to control the maximum number of recursion levels.
WITH EmployeeHierarchy AS
(
SELECT
EmployeeId,
EmployeeName,
ManagerId,
0 AS Level
FROM Employees
WHERE ManagerId IS NULL
UNION ALL
SELECT
E.EmployeeId,
E.EmployeeName,
E.ManagerId,
EH.Level + 1
FROM Employees E
INNER JOIN EmployeeHierarchy EH
ON E.ManagerId = EH.EmployeeId
)
SELECT *
FROM EmployeeHierarchy
OPTION (MAXRECURSION 100);
Common Use Cases
- Employee-manager hierarchies.
- Organization structures.
- Product category hierarchies.
- Folder and directory structures.
- Parent-child relationships.
Advantages of Recursive CTE
- Useful for hierarchical data.
- Avoids writing multiple queries for different levels.
- Makes hierarchical queries easier to understand.
- Can dynamically process multiple hierarchy levels.
- Useful for parent-child relationships.
Important Points
- A recursive CTE refers to itself.
- It contains an anchor member and a recursive member.
- UNION ALL is commonly used between the two members.
- Recursive CTEs are useful for hierarchical data.
- MAXRECURSION can be used to control recursion depth.
Summary
A Recursive CTE is a CTE that refers to itself. It consists of an anchor member that provides the starting records and a recursive member that retrieves related records.
Recursive CTEs are especially useful for employee hierarchies, organization structures, categories, folders and other parent-child relationships.