ποΈ SQL Server: Fix a CTE Referenced Twice in the Same Query Computing Its Result Set Twice Because SQL Server Doesn’t Materialize a Non-Recursive CTE by Default
A Common Table Expression is effectively a named, inline view for the duration of a single query β SQL Server doesn’t automatically compute it once and cache the result for reuse within that same query. If the same CTE name is referenced twice, say once to compute a value and once to join against it, the optimizer is free to, and typically does, expand and re-execute the CTE’s underlying logic independently for each reference, doubling the actual work for what reads like a single reusable definition.
π The Problem
WITH RecentOrders AS (
SELECT CustomerId, SUM(Total) AS Spend
FROM Orders
WHERE OrderDate > DATEADD(month, -3, GETDATE())
GROUP BY CustomerId
)
SELECT c.Name, r.Spend
FROM Customers c
JOIN RecentOrders r ON r.CustomerId = c.Id
WHERE r.Spend > (SELECT AVG(Spend) FROM RecentOrders); -- recomputed independently here
β Fix: Materialize the Expensive Part Once
- Materialize the result explicitly into a temp table (
SELECT ... INTO #RecentOrders) or a table variable when a CTE referenced more than once has an underlying computation expensive enough to matter, so every reference reads the same already-computed rows instead of re-deriving them. - Check the actual execution plan, not just the query text, for the CTE’s underlying subquery appearing more than once β that’s the direct evidence the optimizer chose to expand rather than share it, confirming whether this fix is actually worth applying for a given query.
- Leave a genuinely cheap CTE β a small table, a simple filter β as-is; materializing adds its own temp table I/O cost, so this fix pays off specifically when the CTE’s own computation (an aggregation, a join, a window function) is the expensive part being duplicated.
π¨ Why This Is Dangerous
Because the query still returns correct results either way, this is purely a performance issue that can go unnoticed until the underlying tables grow large enough that the duplicated computation’s cost becomes the dominant cost in the whole query β often first noticed as βthis query used to be fineβ rather than as an obvious logic bug.
A CTE reads like you defined something once β SQL Server reads it as you defining something every time you mention it, and a non-recursive CTE referenced twice is, as far as the optimizer’s concerned, two separate requests to go compute the same thing again.
