Recursive CTEs in SQL Server: How the Parts Actually Work Together
I have written dozens of recursive CTEs over the years, and I still pause every time to think through which part does what. The syntax is not complicated in the way that a 200-line stored procedure is complicated. It is complicated in the way that a knot is complicated: a small number of pieces that loop through each other in a way your eyes do not naturally follow.
This post is the reference I wish I had the first time I tried to write one. We will take the syntax apart piece by piece, walk through the execution model, and build a practical example.
The Shape of a Recursive CTE
Every recursive CTE has exactly three parts. Here is the skeleton:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
WITH [cte_name] AS ( /* 1. Anchor member - runs once, produces the starting rows */ SELECT [columns] FROM [some_table] WHERE [starting_condition] UNION ALL /* 2. Recursive member - runs repeatedly, joining back to the CTE */ SELECT [columns] FROM [some_table] AS [t] INNER JOIN [cte_name] AS [c] ON [t].[parent_key] = [c].[child_key] ) /* 3. Outer query - consumes the accumulated result */ SELECT [columns] FROM [cte_name] OPTION (MAXRECURSION 100); |
That is it. Three parts: the anchor, the recursive member, and the outer query. Let us look at each one.
Part 1: The Anchor Member
The anchor member is the SELECT above the UNION ALL. It runs exactly once and produces the seed rows that start the recursion.
Think of it as answering the question: “Where do I begin?” In an org chart, that is the CEO. In a bill of materials, that is the finished product. In a file system, that is the root folder.
The anchor member does not reference the CTE name. It is an ordinary SELECT against real tables. If it returns zero rows, the entire CTE returns zero rows; the recursive member never executes.
Part 2: The Recursive Member
The recursive member is the SELECT below the UNION ALL. It references the CTE by name, which is what makes the whole thing recursive.
Here is the critical mental model: the recursive member does not see the full accumulated result set. It only sees the rows produced by the previous iteration.
On iteration 1, it joins against the anchor rows. On iteration 2, it joins against the rows that iteration 1 produced. On iteration 3, it joins against the rows that iteration 2 produced. And so on, until an iteration produces zero new rows.
This is why UNION ALL is required, not UNION. Each iteration’s output is appended to the growing result. SQL Server is building a stack of result sets behind the scenes, one per iteration, and gluing them together.
Part 3: The Outer Query
The outer query is the SELECT after the CTE definition. It consumes the complete accumulated result: every row from the anchor, plus every row from every iteration of the recursive member.
You can filter, sort, aggregate, or join the CTE result here just like any other table expression. The CTE is fully materialized by the time the outer query runs.
Walking Through Execution Step by Step
Let us make this concrete. Suppose we have a simple employee hierarchy:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE #employees ( [employee_id] int NOT NULL PRIMARY KEY , [employee_name] varchar(50) NOT NULL , [manager_id] int NULL ); INSERT INTO #employees ([employee_id], [employee_name], [manager_id]) VALUES (1, 'Dana', NULL) , (2, 'Marcus', 1) , (3, 'Priya', 1) , (4, 'Tomoko', 2) , (5, 'Aisha', 2) , (6, 'Lin', 3); |
Dana is the top of the tree (no manager). Marcus and Priya report to Dana. Tomoko and Aisha report to Marcus. Lin reports to Priya.
Now, the recursive CTE:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
WITH [org] AS ( /* Anchor: start with the top-level employee */ SELECT [e].[employee_id] , [e].[employee_name] , [e].[manager_id] , 0 AS [depth] FROM #employees AS [e] WHERE [e].[manager_id] IS NULL UNION ALL /* Recursive member: find direct reports of the previous level */ SELECT [e].[employee_id] , [e].[employee_name] , [e].[manager_id] , [o].[depth] + 1 FROM #employees AS [e] INNER JOIN [org] AS [o] ON [e].[manager_id] = [o].[employee_id] ) SELECT [org].[employee_id] , [org].[employee_name] , [org].[manager_id] , [org].[depth] FROM [org] ORDER BY [org].[depth] , [org].[employee_name]; |
Here is what happens inside the engine:
Iteration 0 (anchor): The anchor finds Dana (manager_id IS NULL). Result so far: 1 row.
Iteration 1: The recursive member joins #employees against the previous iteration’s output (Dana). It finds Marcus and Priya (both have manager_id = 1, which is Dana’s employee_id). Result so far: 3 rows.
Iteration 2: The recursive member joins #employees against iteration 1’s output (Marcus, Priya). It finds Tomoko and Aisha (manager_id = 2, Marcus) and Lin (manager_id = 3, Priya). Result so far: 6 rows.
Iteration 3: The recursive member joins #employees against iteration 2’s output (Tomoko, Aisha, Lin). Nobody reports to any of them. Zero new rows. Recursion stops.
The outer query receives all 6 rows and sorts them by depth and name.
The Depth Column
Notice the [depth] column. The anchor sets it to 0. The recursive member increments it by 1 each iteration. This gives you the level in the hierarchy without any extra work.
This pattern, adding a counter in the recursive member, is useful any time you need to know how many steps away from the starting point a row is.
Building a Path
Another common pattern is building a breadcrumb path as you recurse. Replace the depth column (or add alongside it) with a string that concatenates names:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
WITH [org] AS ( SELECT [e].[employee_id] , [e].[employee_name] , [e].[manager_id] , CONVERT(varchar(500), [e].[employee_name]) AS [path] FROM #employees AS [e] WHERE [e].[manager_id] IS NULL UNION ALL SELECT [e].[employee_id] , [e].[employee_name] , [e].[manager_id] , CONVERT(varchar(500), [o].[path] + ' > ' + [e].[employee_name]) FROM #employees AS [e] INNER JOIN [org] AS [o] ON [e].[manager_id] = [o].[employee_id] ) SELECT [org].[employee_id] , [org].[employee_name] , [org].[path] FROM [org] ORDER BY [org].[path]; |
This produces paths like Dana > Marcus > Tomoko. The CONVERT to varchar(500) is necessary because the recursive member must produce columns with the same data type and length as the anchor. Without it, the first iteration’s shorter string type would truncate deeper paths.
MAXRECURSION: Your Safety Net
By default, SQL Server limits recursive CTEs to 100 iterations. If your recursion exceeds this, you get an error:
|
1 |
The statement terminated. The maximum recursion 100 has been exhausted before statement completion. |
You can change the limit with the OPTION clause:
|
1 |
SELECT [columns] FROM [cte_name] OPTION (MAXRECURSION 500); |
Setting MAXRECURSION to 0 removes the limit entirely. Do not do this casually. If your data has a cycle (employee A reports to B, B reports to A), a recursive CTE with MAXRECURSION 0 will run until it consumes all available tempdb space or hits a timeout. The default limit of 100 exists to catch exactly this kind of problem.
A reasonable approach: set MAXRECURSION to a value slightly above the deepest hierarchy you expect. If your org chart has at most 15 levels, MAXRECURSION 25 gives you room to grow while still catching runaway recursion.
Common Mistakes
Forgetting UNION ALL. You cannot use UNION (without ALL) in a recursive CTE. SQL Server requires UNION ALL. This is a syntax error, not a choice.
Mismatched column types. The anchor and recursive member must produce columns with compatible data types. If the anchor returns varchar(50) and the recursive member tries to return varchar(500), the recursive member’s values will be silently truncated to 50 characters. Use explicit CONVERT in both the anchor and the recursive member to ensure they match.
Joining on the wrong key. The recursive member must join the base table to the CTE on the parent-child relationship. If you accidentally swap the join columns (child joining to child instead of child joining to parent), you get either infinite recursion or no results.
Filtering in the wrong place. If you add a WHERE clause to the recursive member, it filters rows at each iteration, which can prune entire branches of the tree. If you want to filter the final result without affecting the traversal, put the filter in the outer query instead.
When Not to Use Recursive CTEs
Recursive CTEs are elegant for ad-hoc hierarchy queries, but they have limits. Each iteration is essentially a nested loop join against the previous level. For deep hierarchies or large data sets, performance can degrade.
If you query the same hierarchy frequently and performance matters, consider:
- Materializing the hierarchy into a closure table (every ancestor-descendant pair stored explicitly)
- Using the
hierarchyiddata type, which SQL Server provides specifically for hierarchical data (more on this in a follow-up post) - Flattening with a pre-computed depth column if you only need level-based queries
For one-off exploration, reporting, or moderate data sizes, recursive CTEs are the right tool.
Wrapping Up
The recursive CTE is three parts: an anchor that starts the walk, a recursive member that takes one step at a time (seeing only the previous step’s rows), and an outer query that consumes the accumulated result. Once that mental model clicks, the syntax follows naturally.
In a follow-up post, we will look at SQL Server’s hierarchyid data type, which offers a different (and sometimes faster) approach to the same class of problems.
If you have a recursive CTE pattern you find particularly useful, or one that tripped you up, I would like to hear about it. You can find me on Bluesky and LinkedIn.