Common Table Expressions (CTEs) and Recursive CTEs in SQL Server Explained

A CTE is a named subquery that makes complex queries genuinely readable — and recursive CTEs solve a problem plain SQL can’t touch at all: hierarchies of unknown, variable depth.

A Basic CTE

WITH RegionSummary AS (
    SELECT region, SUM(amount) AS total_sales
    FROM dbo.Sale
    GROUP BY region
)
SELECT region, total_sales FROM RegionSummary WHERE total_sales > 10000;

Notice this achieves what Chapter 4’s Fundamentals lesson said WHERE can’t do directly — filter on an aggregate — without needing HAVING, because the aggregate is computed inside the CTE first, then the outer query treats total_sales as an ordinary column. A CTE is purely an organizational tool for readability; this exact query could be written as a subquery in FROM instead, with identical results.

Recursive CTE: Walking a Hierarchy

CREATE TABLE dbo.OrgChart (
    employee_id INT PRIMARY KEY,
    name        NVARCHAR(50) NOT NULL,
    manager_id  INT NULL REFERENCES dbo.OrgChart(employee_id)
);
INSERT INTO dbo.OrgChart VALUES (1,'Priya (CEO)',NULL), (2,'Miguel',1), (3,'Aisha',1), (4,'Liam',2);

WITH OrgHierarchy AS (
    -- Anchor: top of the hierarchy
    SELECT employee_id, name, manager_id, 0 AS level
    FROM dbo.OrgChart WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: joins back to the CTE itself, one level deeper each pass
    SELECT o.employee_id, o.name, o.manager_id, oh.level + 1
    FROM dbo.OrgChart o
    INNER JOIN OrgHierarchy oh ON o.manager_id = oh.employee_id
)
SELECT REPLICATE('  ', level) + name AS org_tree, level FROM OrgHierarchy ORDER BY level, name;

This is a problem that genuinely cannot be solved with a fixed number of JOINs, because you don’t know upfront how many management levels deep an org chart goes — it could be 3 levels or 15, and a plain query has to be written for a specific, known number of hops. Recursion is the only tool in standard SQL that handles “unknown depth.”

How Recursion Actually Runs

SQL Server stops automatically once a pass produces zero new rows. A CTE doesn’t persist anywhere and can’t be indexed — it only exists for the single statement it’s attached to, unlike the temp tables from Chapter 3.

The Infinite Loop Trap

-- A circular reference (Aisha reports to Liam, who reports to Aisha) would recurse forever
-- without a safety net. SQL Server defaults to a hard cap of 100 recursion levels:
SELECT * FROM dbo.OrgChart OPTION (MAXRECURSION 100); -- the implicit default

-- Msg 530 fires automatically once that cap is hit on genuinely bad/circular data:
-- "The statement terminated. The maximum recursion 100 has been exhausted..."

-- You can raise or lower the cap explicitly (0 = unlimited, use with real caution)
;WITH OrgHierarchy AS (...)
SELECT * FROM OrgHierarchy OPTION (MAXRECURSION 50);