CTE Referenced Twice: Why the Query Runs It Twice

A CTE referenced twice is not a saved result, it is a name for a query that can run twice. Write the name two times and SQL Server may do the work two times.

What a CTE really is

A teammate once asked me why a report got slower after they “cleaned it up” into a tidy CTE. The query looked shorter. The CTE was used twice, once for each side of a self join. They assumed the CTE ran once and kept its answer, like a variable.

It does not. A CTE is a label. Wherever you use the label, the engine can expand the query inside it. Often the optimizer finds a clever plan. Sometimes it just does the work again.

Try it on a tiny table

The report needs each group’s total next to the next group’s total. I run it two ways: with the CTE used twice, and with the totals stored first in a temp table. STATISTICS IO is on, so the reads of every step show up in the Messages tab. The temp table load and its index are counted too.

DROP TABLE IF EXISTS #Sales;
CREATE TABLE #Sales (GroupId int, Amount decimal(12,2));
INSERT #Sales VALUES (1, 10), (1, 20), (2, 30), (3, 40);

SET STATISTICS IO ON;

WITH Totals AS
(SELECT GroupId, SUM(Amount) AS TotalAmount FROM #Sales GROUP BY GroupId)
SELECT a.GroupId, a.TotalAmount, b.TotalAmount AS NextTotal
FROM Totals AS a
LEFT JOIN Totals AS b ON b.GroupId = a.GroupId + 1
ORDER BY a.GroupId;

SELECT GroupId, SUM(Amount) AS TotalAmount INTO #GroupTotals
FROM #Sales
GROUP BY GroupId;

CREATE UNIQUE CLUSTERED INDEX CX_GroupTotals ON #GroupTotals (GroupId);

SELECT a.GroupId, a.TotalAmount, b.TotalAmount AS NextTotal
FROM #GroupTotals AS a
LEFT JOIN #GroupTotals AS b ON b.GroupId = a.GroupId + 1
ORDER BY a.GroupId;

SET STATISTICS IO OFF;
DROP TABLE #GroupTotals;
DROP TABLE #Sales;

Both versions return the same three rows: totals of 30.00, 30.00 and 40.00. The last NextTotal is NULL because no group 4 exists.

Now read the Messages. The screenshot shows four lines in order. The CTE version scanned the source table 4 times for 4 logical reads. The staged version reads the source once, reads it again to build the index, and then spends 9 reads on the final join. So on four rows, the CTE actually wins, 4 reads against 11. Staging cost more than it saved.

Now give it a real source

Tiny tables hide the point. This block loads 200,000 rows into 1,000 groups and runs the same two patterns. The ROW_NUMBER trick gives every group the same size, so the result is the same every time you run it.

DROP TABLE IF EXISTS #Big;
CREATE TABLE #Big (GroupId int, Amount decimal(12,2));
INSERT #Big (GroupId, Amount)
SELECT TOP (200000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 1000, 10
FROM sys.all_objects AS x
CROSS JOIN sys.all_objects AS y;

SET STATISTICS IO ON;

WITH Totals AS
(SELECT GroupId, SUM(Amount) AS TotalAmount FROM #Big GROUP BY GroupId)
SELECT COUNT(*) AS GroupRows, SUM(a.TotalAmount + ISNULL(b.TotalAmount, 0)) AS CombinedTotal
FROM Totals AS a
LEFT JOIN Totals AS b ON b.GroupId = a.GroupId + 1;

SELECT GroupId, SUM(Amount) AS TotalAmount INTO #BigTotals
FROM #Big
GROUP BY GroupId;

CREATE UNIQUE CLUSTERED INDEX CX_BigTotals ON #BigTotals (GroupId);

SELECT COUNT(*) AS GroupRows, SUM(a.TotalAmount + ISNULL(b.TotalAmount, 0)) AS CombinedTotal
FROM #BigTotals AS a
LEFT JOIN #BigTotals AS b ON b.GroupId = a.GroupId + 1;

SET STATISTICS IO OFF;
DROP TABLE #BigTotals;
DROP TABLE #Big;

Both queries return 1,000 groups and the same combined total, 3,998,000. Now look at the first message. On my server the CTE version scanned the big table twice, once for each use of the name, and read 1,088 pages. The staged version scanned it once and read 544. After that it only touched a tiny table: 4 reads to build the index and 12 for the final join.

Your page counts will differ. The shape is what matters: the more expensive the expression, the more a second run hurts.

Staging has its own price

A temp table is not free. You pay to write it, to index it, and to read it back. It also freezes the answer at the moment you load it. If the source changes afterward, the staged copy does not follow. For a report that must be consistent from start to finish, that boundary matters.

So here is my rule. Start with the CTE, because it is clear. When the same expensive piece shows up twice, test the staged version at real size. Measure loading, indexing and every consumer together, and pick the one that actually wins on your server.

Next time a CTE appears twice, look at the Messages tab before you decide it ran once.

A CTE is not a stored result, it is a name for a query.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.