SpDev Install free

CTEs in SQL Server: WITH, recursive queries and the performance myth

What a common table expression is, how to write recursive CTEs for hierarchies and date series, MAXRECURSION, and why a CTE used twice is computed twice.

By Areeb Ahmer, . 5 minute read.

A common table expression (CTE) is a named query you define with WITH and then use in the statement that follows, like a temporary view that exists for one statement. CTEs make long queries readable, and recursive CTEs walk hierarchies. They do not store their result, and that’s the part people get wrong.

The examples run against the SpDevDemo database.

Syntax

WITH customer_spend AS (
    SELECT CustomerId, COUNT(*) AS Orders, SUM(Total) AS Spent
    FROM dbo.Orders
    GROUP BY CustomerId
)
SELECT TOP (5) c.Name, s.Orders, s.Spent
FROM customer_spend AS s
JOIN dbo.Customers AS c ON c.CustomerId = s.CustomerId
ORDER BY s.Spent DESC;
  • The CTE exists only for the single statement right after it.
  • If the WITH follows another statement in the batch, that statement must end with a semicolon. That’s why you see ;WITH in so much code: a defensive semicolon.
  • Several CTEs are separated by commas, and each can use the ones before it:
WITH monthly AS (
    SELECT DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1) AS Month, SUM(Total) AS Revenue
    FROM dbo.Orders
    GROUP BY DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1)
),
with_previous AS (
    SELECT Month, Revenue, LAG(Revenue) OVER (ORDER BY Month) AS PreviousRevenue
    FROM monthly
)
SELECT TOP (6) Month, Revenue,
       CAST(100.0 * (Revenue - PreviousRevenue) / PreviousRevenue AS DECIMAL(6, 1)) AS GrowthPct
FROM with_previous
WHERE Month < '2026-11-01'          -- the demo data ends on 1 November
ORDER BY Month DESC;

A CTE can also be the target of UPDATE or DELETE, which is a neat way to delete duplicates:

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

A CTE is not a temporary table

A CTE is expanded into the query that uses it, like a view. SQL Server doesn’t compute it once and keep the result. Reference it twice and it’s computed twice:

SET STATISTICS IO ON;

WITH spend AS (
    SELECT CustomerId, SUM(Total) AS Spent
    FROM dbo.Orders
    GROUP BY CustomerId
)
SELECT (SELECT MAX(Spent) FROM spend) AS MaxSpent,
       (SELECT AVG(Spent) FROM spend) AS AvgSpent;
Table 'Orders'. Scan count 2, logical reads 2680

Two scans of dbo.Orders, 2 × 1,340 pages. Materialize it in a temp table and the aggregation runs once:

SELECT CustomerId, SUM(Total) AS Spent
INTO #spend
FROM dbo.Orders
GROUP BY CustomerId;

SELECT (SELECT MAX(Spent) FROM #spend) AS MaxSpent,
       (SELECT AVG(Spent) FROM #spend) AS AvgSpent;

SET STATISTICS IO OFF;
Table 'Orders'. Scan count 1, logical reads 1340
Table '#spend______...'. Scan count 2, logical reads 150

So:

  • A CTE is for readability. It is neither faster nor slower than writing the same subquery inline.
  • If an expensive CTE is used more than once, or a later step needs an index on its result, use a temp table.
  • Recursive CTEs are the exception: their intermediate results are stored in a work table as they’re built.

Recursive CTEs

A recursive CTE has two parts joined by UNION ALL: an anchor query that produces the starting rows, and a recursive query that references the CTE itself to produce the next level. It repeats until the recursive part returns no rows.

A date series

A calendar of every day in a month, which you can outer-join to sales to show days without any:

WITH days AS (
    SELECT CAST('2026-10-01' AS date) AS Day
    UNION ALL
    SELECT DATEADD(DAY, 1, Day) FROM days WHERE Day < '2026-10-31'
)
SELECT d.Day, COUNT(o.OrderId) AS Orders
FROM days AS d
LEFT JOIN dbo.Orders AS o ON o.OrderDate = d.Day
GROUP BY d.Day
ORDER BY d.Day;

On SQL Server 2022, GENERATE_SERIES does the same job without recursion: SELECT DATEADD(DAY, value, '2026-10-01') FROM GENERATE_SERIES(0, 30).

A hierarchy

The classic case is an org chart or a category tree stored as parent-child rows:

CREATE TABLE #Staff (Id INT PRIMARY KEY, Name NVARCHAR(50), ManagerId INT NULL);
INSERT #Staff VALUES (1, N'Sara', NULL), (2, N'Bilal', 1), (3, N'Hina', 1), (4, N'Omar', 2), (5, N'Zara', 4);

WITH chain AS (
    SELECT Id, Name, ManagerId, 0 AS Level, CAST(Name AS NVARCHAR(400)) AS Path
    FROM #Staff
    WHERE ManagerId IS NULL                         -- anchor: the top
    UNION ALL
    SELECT s.Id, s.Name, s.ManagerId, c.Level + 1, CAST(c.Path + N' > ' + s.Name AS NVARCHAR(400))
    FROM #Staff AS s
    JOIN chain AS c ON s.ManagerId = c.Id           -- recursive: the next level down
)
SELECT Level, Path FROM chain ORDER BY Path;
Level  Path
-----  ------------------------
0      Sara
1      Sara > Bilal
2      Sara > Bilal > Omar
3      Sara > Bilal > Omar > Zara
1      Sara > Hina

The CAST in both parts matters: the anchor and recursive columns must have exactly the same type, or you get Types don’t match between the anchor and the recursive part.

MAXRECURSION

By default, recursion stops with an error after 100 levels, which protects you from infinite loops (for example, from a cycle in the data):

WITH n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 200)
SELECT MAX(i) FROM n;
Msg 530, Level 16, State 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Raise the limit with a query hint, or set it to 0 for no limit, when you’re sure the recursion ends:

WITH n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 200)
SELECT MAX(i) AS max_i FROM n
OPTION (MAXRECURSION 500);

For a hierarchy that might contain cycles, keep the path of IDs and stop when the next ID is already in it.

CTE, subquery, view or temp table?

Use When
CTE A step in one query that’s clearer with a name; recursion
Subquery A small step used once
View The same query shape reused by many queries
Temp table A result used several times, needing an index, or big enough that the optimizer benefits from its statistics

In a stored procedure, the difference between a CTE referenced three times and a temp table often shows up as one statement taking far more reads than it should. SpDev Pro shows reads per line, so a CTE that’s computed three times is easy to spot.