CTEs and Recursive Queries in SQL Server

Common Table Expressions (CTEs) are named, temporary result sets defined with the WITH keyword. They make complex queries more readable by breaking them into logical steps. Recursive CTEs extend this concept to traverse hierarchical or graph-structured data like org charts, bill of materials, or category trees.

Basic CTE Syntax

A CTE is defined before the main SELECT and exists only for the duration of that statement. You can reference a CTE multiple times in the same query.

WITH TopCustomers AS (
    SELECT
        CustomerID,
        SUM(OrderTotal) AS TotalSpent
    FROM Orders
    GROUP BY CustomerID
    HAVING SUM(OrderTotal) > 10000
)
SELECT
    c.CustomerName,
    c.Email,
    tc.TotalSpent
FROM TopCustomers tc
INNER JOIN Customers c ON tc.CustomerID = c.CustomerID
ORDER BY tc.TotalSpent DESC;

Multiple CTEs

You can chain multiple CTEs separated by commas. Later CTEs can reference earlier ones, building a pipeline of transformations.

WITH MonthlySales AS (
    SELECT
        YEAR(OrderDate) AS SalesYear,
        MONTH(OrderDate) AS SalesMonth,
        SUM(OrderTotal) AS MonthlyRevenue
    FROM Orders
    GROUP BY YEAR(OrderDate), MONTH(OrderDate)
),
WithGrowth AS (
    SELECT
        *,
        LAG(MonthlyRevenue) OVER (ORDER BY SalesYear, SalesMonth) AS PrevRevenue
    FROM MonthlySales
)
SELECT
    SalesYear,
    SalesMonth,
    MonthlyRevenue,
    PrevRevenue,
    CASE
        WHEN PrevRevenue > 0
        THEN ROUND((MonthlyRevenue - PrevRevenue) * 100.0 / PrevRevenue, 2)
        ELSE NULL
    END AS GrowthPercent
FROM WithGrowth
ORDER BY SalesYear, SalesMonth;

Recursive CTE: The Basics

A recursive CTE has two parts: an anchor member (the starting rows) and a recursive member (which references the CTE itself). SQL Server executes the anchor first, then repeatedly executes the recursive part until no new rows are produced.

-- Generate numbers 1 to 100
WITH Numbers AS (
    -- Anchor
    SELECT 1 AS n
    UNION ALL
    -- Recursive
    SELECT n + 1 FROM Numbers WHERE n < 100
)
SELECT n FROM Numbers
OPTION (MAXRECURSION 100);

Traversing an Org Chart

The classic use case for recursive CTEs is navigating hierarchical data. Here we traverse an employee-manager relationship to build the full reporting chain.

WITH OrgChart AS (
    -- Anchor: start from the CEO (no manager)
    SELECT
        EmployeeID,
        FirstName,
        LastName,
        ManagerID,
        0 AS Level,
        CAST(FirstName + ' ' + LastName AS NVARCHAR(500)) AS ReportingPath
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive: find direct reports
    SELECT
        e.EmployeeID,
        e.FirstName,
        e.LastName,
        e.ManagerID,
        oc.Level + 1,
        CAST(oc.ReportingPath + ' > ' + e.FirstName + ' ' + e.LastName AS NVARCHAR(500))
    FROM Employees e
    INNER JOIN OrgChart oc ON e.ManagerID = oc.EmployeeID
)
SELECT
    Level,
    REPLICATE('  ', Level) + FirstName + ' ' + LastName AS Employee,
    ReportingPath
FROM OrgChart
ORDER BY ReportingPath;

Bill of Materials

Another common pattern is exploding a bill of materials where a product is composed of sub-components, which themselves may have sub-components.

WITH BOM AS (
    SELECT ComponentID, ComponentName, ParentID, Quantity, 1 AS Depth
    FROM Components
    WHERE ParentID IS NULL AND ComponentID = 1

    UNION ALL

    SELECT c.ComponentID, c.ComponentName, c.ParentID,
           c.Quantity * b.Quantity, b.Depth + 1
    FROM Components c
    INNER JOIN BOM b ON c.ParentID = b.ComponentID
)
SELECT * FROM BOM ORDER BY Depth, ComponentName;

MAXRECURSION

By default, SQL Server limits recursion to 100 levels to prevent infinite loops. You can change this limit with the OPTION (MAXRECURSION n) hint. Set it to 0 for unlimited recursion, but only when you are confident the recursion will terminate.

WITH DeepHierarchy AS (
    SELECT ID, ParentID, Name, 0 AS Level
    FROM Categories WHERE ParentID IS NULL
    UNION ALL
    SELECT c.ID, c.ParentID, c.Name, dh.Level + 1
    FROM Categories c
    INNER JOIN DeepHierarchy dh ON c.ParentID = dh.ID
)
SELECT * FROM DeepHierarchy
OPTION (MAXRECURSION 500);

CTEs are an essential tool for writing maintainable SQL. Use non-recursive CTEs to decompose complex logic, and recursive CTEs whenever you need to traverse hierarchical or graph data. They are often cleaner and more readable than equivalent subquery-based approaches.