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.
Questions this post answers
Why does my SQL Server CTE query do more logical reads than the entire table even with indexes?
SQL Server's optimizer can misestimate cardinality inside a CTE because it doesn't know the actual value being filtered — it assumes an average case. In a query finding the most-popular location and then its top users, SQL Server estimated 14 rows but found 113,399, causing 113,399 key lookups and 478,982 logical reads — exceeding the full clustered index size despite supporting indexes being present. Developers chasing down unexpectedly high logical reads in SQL Server queries track patterns like this on daily.dev.
How does replacing a CTE with a temp table improve SQL Server query performance?
When a temp table holds intermediate results, SQL Server automatically builds statistics on its contents before executing the next query. This lets the optimizer learn the actual value stored (e.g., Location = 'India'), look it up in column statistics, and build a plan tailored to that specific data distribution — in one case reducing logical reads by two-thirds compared to the equivalent CTE plan. SQL Server developers weighing CTE vs temp table trade-offs for real workloads find concrete comparisons like this on daily.dev.