When CTEs Don't Work, Try Temp Tables

CTEs allow SQL Server to reorder query processing for optimal efficiency, but sometimes the optimizer makes poor estimates. Using the Stack Overflow database on SQL Server 2025, a CTE query to find the top 250 users in the most-populated location resulted in 478,982 logical reads — more than the entire clustered index — because SQL Server estimated only 14 rows for the most popular location instead of 113,399, triggering excessive key lookups. Replacing the CTE with a temp table reduced logical reads by two-thirds. The temp table forced SQL Server to build fresh statistics on the intermediate result, revealing the actual location value ('India'), which let the optimizer build a plan tailored to a high-cardinality location — scanning the Reputation index in reverse and using a Top cutoff instead of a sort. The key takeaway: default to CTEs, but switch to temp tables when the optimizer's estimates are badly off (10x or more), keeping in mind that temp tables effectively trigger recompilation and are not universally superior.

Source: https://www.brentozar.com/archive/2026/08/when-ctes-dont-work-try-temp-tables-tsql2sday. 8 Sync News only summarizes and links out; content copyright belongs to the authors and original sources.