CTEs in SQL Server

A common table expression (CTE) is a named result set that exists only for the duration of a single statement. It replaces nested derived tables with readable, top-to-bottom logic, and it is the only way in T-SQL to write recursive queries.

SQL Server has supported CTEs since 2005, so version compatibility is a non-issue.

Basic Syntax

WITH regional_totals AS (
    SELECT region, SUM(amount) AS total
    FROM sales
    GROUP BY region
)
SELECT region, total
FROM regional_totals
WHERE total > 300;

The CTE is visible only to the statement immediately following it. Two CTEs with the same name in different statements are unrelated.

The Semicolon Quirk

WITH also begins other T-SQL constructs (table hints, WITH XMLNAMESPACES), so if the previous statement in a batch is not terminated, the parser misreads it:

Incorrect syntax near the keyword 'with'. If this statement is a common
table expression ... the previous statement must be terminated with a semicolon.

The fix is to terminate the preceding statement with ;. You will see many scripts write ;WITH as a defensive habit. It works, but the honest fix is terminating every statement -- Microsoft has deprecated non-semicolon-terminated statements for years.

Chaining Multiple CTEs

One WITH, comma-separated definitions. Later CTEs can reference earlier ones:

WITH daily AS (
    SELECT sale_date, SUM(amount) AS day_total
    FROM sales
    GROUP BY sale_date
),
ranked AS (
    SELECT sale_date, day_total,
           RANK() OVER (ORDER BY day_total DESC) AS rnk
    FROM daily
)
SELECT sale_date, day_total
FROM ranked
WHERE rnk <= 3;

This CTE-then-window-function pattern is the standard workaround for T-SQL's lack of QUALIFY: window functions cannot appear in WHERE, so you name the intermediate result and filter one level up.

CTEs Are Inlined, Not Materialized

SQL Server expands a CTE into the outer query like a macro. It does not compute the result once and cache it. Reference a CTE three times and its query can be evaluated three times, each potentially re-scanning the base tables.

Consequences:

• A cheap CTE referenced multiple times is fine -- the optimizer works with the expanded tree.
• An expensive CTE referenced multiple times can silently multiply its cost. If you see the same heavy scan repeated in the execution plan, materialize the intermediate result yourself in a #temp table (which also gets statistics, unlike a CTE or table variable) and join to that.
• Unlike PostgreSQL, there is no AS MATERIALIZED hint. The temp table is the materialization hint in T-SQL.

CTE vs temp table in one line: CTE for readability and single-statement logic, #temp table for large intermediate results used more than once.

Recursive CTEs

A recursive CTE has an anchor member, UNION ALL, and a recursive member that references the CTE itself:

CREATE TABLE employees (
    id         int primary key,
    name       varchar(50),
    manager_id int NULL
);
 
INSERT INTO employees VALUES
    (1, 'Ada',   NULL),
    (2, 'Grace', 1),
    (3, 'Edgar', 1),
    (4, 'Linus', 2);
 
WITH org AS (
    -- anchor: the root(s)
    SELECT id, name, manager_id, 0 AS depth,
           CAST(name AS varchar(4000)) AS path
    FROM employees
    WHERE manager_id IS NULL
 
    UNION ALL
 
    -- recursive member: joins back to the CTE
    SELECT e.id, e.name, e.manager_id, org.depth + 1,
           CAST(org.path + ' > ' + e.name AS varchar(4000))
    FROM employees e
    JOIN org ON e.manager_id = org.id
)
SELECT * FROM org ORDER BY path;

Execution is iterative: the anchor produces level 0, then the recursive member runs against the previous level's rows until an iteration produces nothing.

Two T-SQL-specific rules trip people up:

1. Column types must match exactly between anchor and recursive member. The CAST(... AS varchar(4000)) on both sides above is not decoration -- without it, string concatenation grows the type and you get "Types don't match between the anchor and the recursive part".
2. The recursive member is restricted: no GROUP BY, no aggregates, no TOP, no outer join against the CTE, and the CTE may be referenced exactly once in it.

MAXRECURSION: the 100-Level Default

SQL Server aborts a recursive CTE after 100 recursions by default:

The maximum recursion 100 has been exhausted before statement completion.

Override per query:

SELECT * FROM org
OPTION (MAXRECURSION 1000);   -- up to 32767, or 0 for unlimited

Treat the error as a signal first and a limit second. Real org charts and BOM hierarchies are rarely 100 levels deep -- hitting the cap usually means a cycle in your data (a row that is its own ancestor). MAXRECURSION 0 removes the safety net entirely; if there is a cycle, the query runs until you kill it. For cycle detection, carry a path string (as above) and add WHERE org.path NOT LIKE '%' + e.name + '%'-style guards, or better, use a numeric id path.

One more gotcha: MAXRECURSION is a statement-level hint, so you cannot put it inside a view definition. Put the hint in the query that selects from the view.

Generating Sequences (and Why Not To)

The number-series recursive CTE shows up in every tutorial:

WITH nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 1000
)
SELECT n FROM nums OPTION (MAXRECURSION 0);

It works, but it is row-by-row iteration. From SQL Server 2022, GENERATE_SERIES(1, 1000) does the same thing set-based and faster. On older versions, a tally table or cross-joined VALUES clauses beat recursion for large ranges.

Updating Through a CTE

CTEs can be the target of UPDATE, DELETE, and INSERT, which makes "delete duplicates keeping one" a two-liner:

WITH dupes AS (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY region, sale_date ORDER BY amount DESC
    ) AS rn
    FROM sales
)
DELETE FROM dupes WHERE rn > 1;

The delete applies to the underlying table. This works because the CTE is updatable (single base table, no aggregation) -- the same rules as updatable views.

Common Mistakes

• Assuming the CTE runs once. It is inlined. Expensive CTE + multiple references = multiplied cost; use a #temp table.
• Missing semicolon before WITH in multi-statement batches.
• Type mismatch between anchor and recursive member. CAST both sides explicitly.
• MAXRECURSION 0 as a reflex. Find out why you exceeded 100 first -- it is usually a data cycle.
• ORDER BY inside a CTE. Not allowed without TOP, and even with TOP it does not guarantee outer ordering. Order in the outer query.

CTEs are also where AI-assisted editors earn their keep -- Mako's autocomplete can scaffold the anchor/recursive-member structure from a description of the hierarchy while you fill in the join logic.

Mako connects to SQL Server with AI-powered autocomplete. Try it free at mako.ai.