Common Table Expressions and Recursive Queries in T-SQL

I inherited a report with subqueries nested three levels deep and spent most of an afternoon just working out what it did. A CTE would have made it readable in minutes. A CTE is a named result set defined with WITH that lives for one statement, and a recursive one references itself, which is how T-SQL walks trees of unknown depth, generates number and date ranges and builds paths in a single SELECT.

I'll start with the basic form, move to a recursive hierarchy query, and point out the pitfalls people hit the first time they run one on real data.

Replacing a subquery

WITH MonthlySales AS (
    SELECT CustomerID, DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1) AS SalesMonth, SUM(Amount) AS Total
    FROM dbo.Orders
    GROUP BY CustomerID, DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1)
),
Ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY SalesMonth ORDER BY Total DESC) AS rn
    FROM MonthlySales
)
SELECT SalesMonth, CustomerID, Total
FROM Ranked
WHERE rn <= 3
ORDER BY SalesMonth, rn;

Several CTEs are separated by commas and can reference the ones defined before them. The main use is exactly this: filter on a window function's result, which is not allowed directly in WHERE.

Two syntax rules catch people out. The statement before WITH must end with a semicolon, and the CTE is followed immediately by exactly one SELECT, INSERT, UPDATE, DELETE or MERGE. A CTE is not a temp table; if you need it twice, the engine evaluates it twice.

Update and delete through a CTE

A CTE can be the target of a modification, which makes it my go-to for deleting duplicates:

WITH Dupes AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY Email ORDER BY CustomerID) AS rn
    FROM dbo.Customers
)
DELETE FROM Dupes WHERE rn > 1;

The same restrictions as updatable views apply: the modification must resolve to one base table.

The recursive form

A recursive CTE has an anchor member, UNION ALL, and a recursive member that references the CTE itself. The anchor runs once; the recursive member runs repeatedly, each time against the rows produced by the previous iteration, until it produces no rows.

Take an employee table with a ManagerID pointing at another employee:

WITH OrgChart AS (
    -- anchor: the top of the tree
    SELECT EmployeeID, ManagerID, Name, 0 AS Level,
           CAST(Name AS nvarchar(4000)) AS Path
    FROM dbo.Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- recursive member: everyone whose manager is in the previous level
    SELECT e.EmployeeID, e.ManagerID, e.Name, oc.Level + 1,
           CAST(oc.Path + N' > ' + e.Name AS nvarchar(4000))
    FROM dbo.Employees e
    JOIN OrgChart oc ON oc.EmployeeID = e.ManagerID
)
SELECT REPLICATE('  ', Level) + Name AS Tree, Level, Path
FROM OrgChart
ORDER BY Path;

Both members must have the same number of columns with compatible types, which is why the Path column is explicitly cast to the same length in both places. Going the other way, from an employee up to the root, is the same shape with the join reversed (oc.ManagerID = e.EmployeeID) and the anchor filtering on the starting employee.

Generating date ranges without a numbers table

Recursion also produces rows that do not exist in any table, such as every day in a range for a report that must show zero-activity days:

DECLARE @From date = '20200601', @To date = '20200630';

WITH Days AS (
    SELECT @From AS d
    UNION ALL
    SELECT DATEADD(day, 1, d) FROM Days WHERE d < @To
)
SELECT dy.d, ISNULL(SUM(o.Amount), 0) AS DailyTotal
FROM Days dy
LEFT JOIN dbo.Orders o ON CAST(o.OrderDate AS date) = dy.d
GROUP BY dy.d
ORDER BY dy.d
OPTION (MAXRECURSION 366);

The MAXRECURSION hint is needed because the default limit is 100 iterations. For ranges that could exceed a few thousand rows, a set-based numbers table or a CROSS JOIN of sys.all_columns is faster; recursion is one row per iteration.

Cycles and recursion depth

Bad data with a loop (A reports to B, B reports to A) makes a recursive CTE run until it hits the limit. Guard against it by carrying the path and stopping when a node is already in it:

WITH OrgChart AS (
    SELECT EmployeeID, ManagerID, Name, 0 AS Level, CAST('|' + CAST(EmployeeID AS varchar(10)) + '|' AS varchar(4000)) AS IdPath
    FROM dbo.Employees WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.Name, oc.Level + 1, CAST(oc.IdPath + CAST(e.EmployeeID AS varchar(10)) + '|' AS varchar(4000))
    FROM dbo.Employees e
    JOIN OrgChart oc ON oc.EmployeeID = e.ManagerID
    WHERE oc.IdPath NOT LIKE '%|' + CAST(e.EmployeeID AS varchar(10)) + '|%'
)
SELECT * FROM OrgChart OPTION (MAXRECURSION 50);

Set MAXRECURSION to the deepest legitimate hierarchy plus a margin. I never use MAXRECURSION 0 unless a cycle guard is in place.

Sanity-checking the result

Count rows per level and compare the total with the table:

WITH OrgChart AS ( ... )
SELECT Level, COUNT(*) FROM OrgChart GROUP BY Level ORDER BY Level;
SELECT COUNT(*) FROM dbo.Employees;

If the CTE returns fewer rows than the table, some employees have a ManagerID that points to a non-existent row (orphans) and never get reached; that is a data quality finding in itself. In the execution plan, the recursive part shows up as an Index Spool with a Nested Loops join; an index on ManagerID turns the scan inside the loop into a seek.

Errors you'll meet

• "Msg 530: The statement terminated. The maximum recursion 100 has been exhausted before statement completion." Either the hierarchy is deeper than 100 levels (unlikely) or there is a cycle in the data. Add the path guard from the cycles section, then find the cycle with a self-join on ManagerID.
• "Msg 240: Types don't match between the anchor and the recursive part in column 'Path' of recursive query 'OrgChart'." The anchor's string literal is varchar(5) and the recursive part produces varchar(4000), or nvarchar versus varchar. Cast both to the same type and length explicitly.
• "Msg 319: Incorrect syntax near the keyword 'with'. If this statement is a common table expression ... the previous statement must be terminated with a semicolon." Add the semicolon before WITH, or start the CTE with ;WITH as many people do.
• "Msg 462: Outer join is not allowed in the recursive part of a recursive common table expression 'OrgChart'." Only inner joins are permitted in the recursive member, and no GROUP BY, TOP, DISTINCT or subqueries referencing the CTE. Do the outer join in the final SELECT instead.

Keep the anchor small and index the join column.