--- title: When CTEs Don't Work, Try Temp Tables | daily.dev description: CTEs allow SQL Server to reorder query processing for optimal efficiency, but sometimes the optimizer makes poor estimates. Using the Stack Overflow database... canonical: https://daily.dev/posts/when-ctes-don-t-work-try-temp-tables-5pxookj6t twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: When CTEs Don't Work, Try Temp Tables | daily.dev og:description: CTEs allow SQL Server to reorder query processing for optimal efficiency, but sometimes the optimizer makes poor estimates. Using the Stack Overflow database... og:url: https://daily.dev/posts/when-ctes-don-t-work-try-temp-tables-5pxookj6t og:image: https://api.daily.dev/og/posts/5pxoOKJ6t.png og:image:alt: When CTEs Don't Work, Try Temp Tables og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # When CTEs Don't Work, Try Temp Tables **[Brent Ozar](https://daily.dev/sources/brentozar)** · 6 min read · 0 upvotes · 0 comments ## Summary 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. ## Full article daily.dev links to this article rather than hosting it. Read it at the original source: ## Questions this post answers ### Why does my SQL Server CTE query read more pages than the entire table when finding top users by reputation in the most popular location? SQL Server underestimates the number of rows for the most-popular location because it knows only one location will be returned but not which one, so it defaults to an average-location estimate instead. In one example, this caused the optimizer to expect 14 rows but actually find far more, triggering 113,399 key lookups and 478,982 logical reads on SQL Server 2025, more than the whole clustered index. _When query plans misestimate like this, developers compare CTE-versus-temp-table fixes on daily.dev before tuning production SQL._ ### Why would replacing a CTE with a temp table improve a SQL Server query plan? A temp table forces SQL Server to build fresh statistics on the intermediate result set, letting it recognize the actual value (such as a populous location like India) and generate a plan tailored to that specific value rather than an average case. In one test this cut logical reads by two-thirds by enabling a backwards index scan with a Top operator instead of excessive key lookups, though it behaves like OPTION (RECOMPILE) and isn't always beneficial. _Anyone weighing CTEs against temp tables for performance can track these tuning trade-offs on daily.dev._ ### How do I diagnose why a SQL Server query plan is slow using estimated versus actual rows? Read the execution plan from right to left, top to bottom, looking for the first point where estimated rows differ from actual rows by 10x or more; that point identifies where the optimizer's assumptions broke down and where a fix like adding a temp table or a hint should target. This method was taught as the single most important takeaway in a query tuning class using SQL Server 2025 and the Stack Overflow database. _Developers debugging skewed SQL Server estimates can follow ongoing query-tuning techniques on daily.dev._ ## Similar posts on daily.dev - [CTE Support in Hibernate](https://daily.dev/posts/cte-support-in-hibernate-4v2x07evu) · Baeldung · 6 upvotes · 0 comments - [10 Rare SQL Features Every Developer Should Know](https://daily.dev/posts/10-rare-sql-features-every-developer-should-know-fvu4qwczx) · Medium · 1 upvotes · 0 comments - [AI Execution Plan Analysis: I Gave It My Plan and Asked What Was Wrong](https://daily.dev/posts/ai-execution-plan-analysis-i-gave-it-my-plan-and-asked-what-was-wrong-goqe4azrt) · SQL Authority · 0 upvotes · 0 comments - [How to optimize when you can’t do anything\!](https://daily.dev/posts/how-to-optimize-when-you-can-t-do-anything--31j5al2rl) · Planet PostgreSQL · 0 upvotes · 0 comments --- Tags: [#microsoft-sql-server](https://daily.dev/tags/microsoft-sql-server) [View this post on daily.dev](https://daily.dev/posts/when-ctes-don-t-work-try-temp-tables-5pxookj6t) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"TechArticle","headline":"When CTEs Don't Work, Try Temp Tables","url":"https://daily.dev/posts/when-ctes-don-t-work-try-temp-tables-5pxookj6t","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/when-ctes-don-t-work-try-temp-tables-5pxookj6t"},"datePublished":"2026-08-11T13:16:49.589Z","dateModified":"2026-09-14T08:56:29.066Z","description":"CTEs allow SQL Server to reorder query processing for optimal efficiency, but sometimes the optimizer makes poor estimates. Using the Stack Overflow database...","image":"https://i0.wp.com/www.brentozar.com/wp-content/uploads/2026/08/cte-execution-plan.jpg?resize=1000%2C255&ssl=1","thumbnailUrl":"https://i0.wp.com/www.brentozar.com/wp-content/uploads/2026/08/cte-execution-plan.jpg?resize=1000%2C255&ssl=1","isAccessibleForFree":true,"articleSection":"Brent Ozar","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"Brent Ozar","logo":"https://media.daily.dev/image/upload/s--uHhajUhc--/c_limit,w_256/f_auto,q_auto/v1780213426/logos/brentozar?_a=BAMAMiWQ0","url":"https://daily.dev/sources/brentozar"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/when-ctes-don-t-work-try-temp-tables-5pxookj6t","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":0},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"microsoft-sql-server","timeRequired":"PT6M"} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"Brent Ozar","item":"https://daily.dev/sources/brentozar"},{"@type":"ListItem","position":3,"name":"When CTEs Don't Work, Try Temp Tables"}]} {"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/when-ctes-don-t-work-try-temp-tables-5pxookj6t#faq","mainEntity":[{"@type":"Question","name":"Why does my SQL Server CTE query read more pages than the entire table when finding top users by reputation in the most popular location?","acceptedAnswer":{"@type":"Answer","text":"SQL Server underestimates the number of rows for the most-popular location because it knows only one location will be returned but not which one, so it defaults to an average-location estimate instead. In one example, this caused the optimizer to expect 14 rows but actually find far more, triggering 113,399 key lookups and 478,982 logical reads on SQL Server 2025, more than the whole clustered index. When query plans misestimate like this, developers compare CTE-versus-temp-table fixes on daily.dev before tuning production SQL."}},{"@type":"Question","name":"Why would replacing a CTE with a temp table improve a SQL Server query plan?","acceptedAnswer":{"@type":"Answer","text":"A temp table forces SQL Server to build fresh statistics on the intermediate result set, letting it recognize the actual value (such as a populous location like India) and generate a plan tailored to that specific value rather than an average case. In one test this cut logical reads by two-thirds by enabling a backwards index scan with a Top operator instead of excessive key lookups, though it behaves like OPTION (RECOMPILE) and isn't always beneficial. Anyone weighing CTEs against temp tables for performance can track these tuning trade-offs on daily.dev."}},{"@type":"Question","name":"How do I diagnose why a SQL Server query plan is slow using estimated versus actual rows?","acceptedAnswer":{"@type":"Answer","text":"Read the execution plan from right to left, top to bottom, looking for the first point where estimated rows differ from actual rows by 10x or more; that point identifies where the optimizer's assumptions broke down and where a fix like adding a temp table or a hint should target. This method was taught as the single most important takeaway in a query tuning class using SQL Server 2025 and the Stack Overflow database. Developers debugging skewed SQL Server estimates can follow ongoing query-tuning techniques on daily.dev."}}]} ```