Recursive CTEs let SQL Server walk hierarchical data using an anchor, a recursive step and a stop condition.
Complete 12 of 24 practices (50%) and enter your name to unlock the Certificate of Participation.
A recursive CTE has two logical parts: the anchor member produces the starting rows; the recursive member repeatedly joins back to the CTE to find the next generation of rows.
The anchor might select the CEO. The recursive member then finds employees who report to the CEO, then employees who report to those managers, and so on.
WITH Org AS
(
-- Anchor
SELECT EmployeeID, ManagerID, EmployeeName, 0 AS LevelNo
FROM dbo.Employee
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member
SELECT e.EmployeeID,
e.ManagerID,
e.EmployeeName,
o.LevelNo + 1
FROM dbo.Employee AS e
JOIN Org AS o
ON e.ManagerID = o.EmployeeID
)
SELECT *
FROM Org;A recursive CTE is not magic: define the start, define the next-step relationship, and make sure the process can terminate.
Employee-manager relationships are a classic parent-child structure. Once the relationship is modeled correctly, the recursive CTE can return the entire tree or any subtree.
After the CEO level is returned, those rows become inputs for finding direct reports. Those direct reports then become inputs for the next iteration.
DECLARE @RootEmployeeID int = 100;
WITH Org AS
(
SELECT EmployeeID,
ManagerID,
EmployeeName,
0 AS LevelNo
FROM dbo.Employee
WHERE EmployeeID = @RootEmployeeID
UNION ALL
SELECT e.EmployeeID,
e.ManagerID,
e.EmployeeName,
o.LevelNo + 1
FROM dbo.Employee AS e
JOIN Org AS o
ON e.ManagerID = o.EmployeeID
)
SELECT *
FROM Org
ORDER BY LevelNo, EmployeeName;Starting from a selected manager turns the same recursive logic into a reusable subtree query.
A useful hierarchy query often needs more than the rows themselves. Carry metadata through each recursive step: level number, path text, root identifier or accumulated values.
A category tree becomes much easier to understand when each row includes both its depth and a breadcrumb such as Electronics > Computers > Laptops.
WITH CategoryTree AS
(
SELECT CategoryID,
ParentCategoryID,
CategoryName,
0 AS LevelNo,
CAST(CategoryName AS varchar(1000)) AS PathText
FROM dbo.Category
WHERE ParentCategoryID IS NULL
UNION ALL
SELECT c.CategoryID,
c.ParentCategoryID,
c.CategoryName,
p.LevelNo + 1,
CAST(p.PathText + ' > ' + c.CategoryName AS varchar(1000))
FROM dbo.Category AS c
JOIN CategoryTree AS p
ON c.ParentCategoryID = p.CategoryID
)
SELECT *
FROM CategoryTree;Depth tells you where the row is; the path tells you how it got there.
Recursive CTEs are valuable wherever one row can contain or parent rows of the same logical type. Bills of materials, folder structures and nested categories all repeat the same relationship.
A manager has employees. A folder has subfolders. An assembly has components. The business nouns differ, but the SQL recursion pattern is the same.
WITH BOM AS
(
SELECT ParentPartID,
ComponentPartID,
QuantityPer,
CAST(QuantityPer AS decimal(18,4)) AS ExtendedQty,
1 AS LevelNo
FROM dbo.BillOfMaterial
WHERE ParentPartID = @RootPartID
UNION ALL
SELECT b.ParentPartID,
b.ComponentPartID,
b.QuantityPer,
CAST(p.ExtendedQty * b.QuantityPer AS decimal(18,4)),
p.LevelNo + 1
FROM dbo.BillOfMaterial AS b
JOIN BOM AS p
ON b.ParentPartID = p.ComponentPartID
)
SELECT *
FROM BOM;Recursive CTEs become especially powerful when they carry business values as well as structural relationships.
Hierarchical data is only safe when the relationship actually forms a tree or controlled graph. Bad parent-child data can create loops such as A → B → C → A.
If a node eventually points back to an ancestor, the recursive rule keeps finding work. Production queries need both data-quality checks and a recursion strategy.
WITH Tree AS
(
SELECT NodeID,
ParentNodeID,
CAST('/' + CAST(NodeID AS varchar(20)) + '/' AS varchar(max)) AS VisitPath
FROM dbo.Node
WHERE NodeID = @RootNodeID
UNION ALL
SELECT n.NodeID,
n.ParentNodeID,
CAST(t.VisitPath + CAST(n.NodeID AS varchar(20)) + '/' AS varchar(max))
FROM dbo.Node AS n
JOIN Tree AS t
ON n.ParentNodeID = t.NodeID
WHERE t.VisitPath NOT LIKE '%/' + CAST(n.NodeID AS varchar(20)) + '/%'
)
SELECT *
FROM Tree
OPTION (MAXRECURSION 200);MAXRECURSION is a safety boundary, not a substitute for fixing cyclic data.
The same hierarchy can answer different business questions depending on where you anchor it and which direction the relationship is traversed.
Start at a department manager to get all descendants. Reverse the relationship to walk upward and return the chain of managers. Filter for nodes with no children to identify leaves.
WITH Managers AS
(
SELECT EmployeeID,
ManagerID,
EmployeeName,
0 AS LevelNo
FROM dbo.Employee
WHERE EmployeeID = @EmployeeID
UNION ALL
SELECT m.EmployeeID,
m.ManagerID,
m.EmployeeName,
c.LevelNo + 1
FROM dbo.Employee AS m
JOIN Managers AS c
ON m.EmployeeID = c.ManagerID
)
SELECT *
FROM Managers;Recursive direction matters: the same table can be walked downward to descendants or upward to ancestors.
Recursive CTE syntax can be compact, but execution cost depends on hierarchy size, branching factor, depth, indexing and filtering. A small org chart and a million-node dependency graph are very different workloads.
A hierarchy where every node has many children can expand much faster than expected. Good indexes on parent-child keys and a selective root can dramatically change the workload.
CREATE INDEX IX_Employee_ManagerID
ON dbo.Employee (ManagerID)
INCLUDE (EmployeeID, EmployeeName);
-- Then inspect:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- Run recursive query and review Actual Execution Plan.Recursive CTEs are easy to write; production-ready recursion requires understanding how many rows each level actually creates.
Recursive CTEs are excellent for readable, on-demand traversal of moderate hierarchies. But SQL Server also offers hierarchyid, and some systems use closure tables, path enumeration or precomputed hierarchy structures when traversal is extremely frequent or large.
Recursive CTEs are often the simplest starting point. If hierarchy traversal becomes a central workload with very large scale, compare alternative data models instead of forcing one pattern forever.
1. Confirm the parent-child key
2. Define the anchor/root
3. Define the recursive relationship
4. Carry Level / Path if needed
5. Validate termination
6. Detect or prevent cycles
7. Set an intentional MAXRECURSION strategy
8. Test row counts, IO and execution plan
9. Compare alternative hierarchy models if scale demands itRecursive CTEs are powerful because they repeat one relationship cleanly; they are safe when termination, data quality and scale are treated as first-class requirements.
Open each item after answering it in your own words. The 24 interactive practices above drive certificate progress.
The anchor member creates the starting rows; the recursive member uses prior rows to find the next level.
Recursion stops when the recursive member produces no additional rows, subject to SQL Server's recursion limit.
A cycle can cause the same nodes to be revisited repeatedly, preventing natural termination and producing runaway recursion.
It sets a recursion-depth limit for the statement and acts as a safety boundary.
The root, parent-child key, traversal direction, cycle behavior, expected depth, row counts, indexing and actual execution plan.
| Layer | Purpose |
|---|---|
| Anchor | Choose the starting root row or root set |
| Recursive Member | Find the next parent-child level |
| Context | Carry Level, Path, Root or accumulated measures |
| Safety | Protect against cycles and runaway depth |
| Performance | Index hierarchy keys and inspect actual execution behavior |
Complete at least 12 of the 24 practice cases (50%) and enter your name.
Recursive CTEs are excellent for clear parent-child traversal, but production safety depends on valid hierarchy data and controlled recursion.