T-SQL & Development
When CTEs Don’t Work, Try Temp Tables – #TSQL2sday
Common Table Expressions are awesome because they let SQL Server reorder processing in whatever way it deems to be the most efficient for your current data distribution, on your current version of SQL Server. Default to CTEs.
When SQL Server gets that process wrong, switch to temp tables.
Let’s start with an example. I’m using SQL Server 2025 and the big Stack Overflow 2024-04 database in 2025 compat level.
The below query says, “Find the most popular Users.Location – the one with the most people in it – and then amongst the people who live there, find the top 250 users with the highest Reputation score.” It also creates a couple of indexes to help.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE INDEX Location ON dbo.Users(Location); CREATE INDEX Reputation ON dbo.Users(Reputation); GO SET STATISTICS IO ON; WITH TopLocation AS (SELECT TOP 1 Location FROM dbo.Users WHERE Location <> '' GROUP BY Location ORDER BY COUNT(*) DESC ) SELECT TOP 250 u.* FROM TopLocation tl INNER JOIN dbo.Users u ON tl.Location = u.Location ORDER BY u.Reputation DESC; |
Does that query produce a good plan? Well, one of the ways we judge it is to look at its logical reads – in this case, we read 478,982 pages:
Is that a lot? Well, for comparison, here’s the size of the whole table:
That means in order to execute this query, SQL Server read more pages than there are in the entire clustered index of the table – despite the fact that we have indexes to help. If we look at the actual execution plan of the CTE query, we can see that SQL Server did indeed use the indexes:
Like I tell students in my Fundamentals of Query Tuning class, if there’s only one thing you take away from the entire class, let it be this: when you’re unhappy with a query’s performance, read the plan from right to left, top to bottom, looking for the first place where estimates versus actuals are 10x off or more.
In our example, as we read from right to left, the first thing that’s happening is the CTE. SQL Server is diving into the first non-null, non-empty-space Location in the Location index, aggregating them together to get counts of the number of people who live in each one, and then sorting them by that population count. At the time this sort finishes, SQL Server knows that it’s going to find 1 location – so far, so good, our estimates have been bang on:
So far, so good, our estimates have been bang on.
The next thing that happens is SQL Server dives into the Location index to find the people who live in that top 1 location. “Hey, SQL Server, how many rows do you think you’re gonna find?”
“FOURTEEN, BAWSS.”
“Uh, SQL Server, how many rows did you actually find for that location?”
“UH… MORE THAN FOURTEEN, BAWSS. SORRY, BAWSS.”
So as a result, it ends up doing 113,399 key lookups to get each user’s reputation. Each of those key lookups results in a few logical reads, and 113,399 * 3 reads each = big data reads, relatively speaking.
The problem: SQL Server knows we’re looking for just 1 location, but it doesn’t know what that location is, and what shocks most people the mostest, it doesn’t really put together the fact that we’re looking for the most-popular location. It thinks we’re looking for the average location. We need a way to help SQL Server to understand that the one location value we’re looking for is something special.
Back to our opening paragraph, I said start with a CTE – and if that isn’t getting the performance results you want, try substituting the CTE with a temp table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DROP TABLE IF EXISTS #TopLocation; CREATE TABLE #TopLocation (Location NVARCHAR(200)); INSERT INTO #TopLocation (Location) SELECT TOP 1 Location FROM dbo.Users WHERE Location <> '' GROUP BY Location ORDER BY COUNT(*) DESC; SELECT TOP 250 u.* FROM #TopLocation tl INNER JOIN dbo.Users u ON tl.Location = u.Location ORDER BY u.Reputation DESC; |
Check the logical reads:
They’re down by 2/3! Let’s look at the execution plan to see what changed:
The first query is populating the temp table, same work as the CTE. That part was never the problem for logical reads, and the estimates were always great.
The second query is where things get spicy. SQL Server chose to scan the Reputation index backwards, from highest reputation to lowest, and for each person it found, it did a key lookup to check whether that person lives in our particular chosen location or not.
That sounds bizarre, but for locations that show up very often, this turns out to be very efficient! Note that the plan also doesn’t require a sort – the data’s already sorted by reputation descending – so there’s no sort in the plan, only a Top, which is like a cutoff valve that drops when we meet our 250 row goal.
In that second query plan, read the estimates vs actuals from right to left, top to bottom, and they’re muuuuuch closer than our CTE query was.
In this case, the temp table wins because:
- At the time of the select running, SQL Server realized it didn’t have any stats on the temp table’s contents
- It automatically built statistics on the temp table, thereby learning the data distribution & contents of our one whopping row
- It understood that the contents were Location = “India”
- It looked up India in the Users.Location statistics, and realized that’s a biiiig location
- It built a brand new query plan for the second query, and that new query plan was designed specifically for popular locations like India
However, this is only one case! I don’t want you drawing complete conclusions that temp tables are always better, because they’re not. In this specific example, I got a fresh execution plan based on the values I’m looking for, but that is not always the case. Even when it IS the case, you’re still dealing with what’s effectively OPTION (RECOMPILE), which can be a real pain in the processor.
So like I said in the beginning, Common Table Expressions are awesome because they let SQL Server reorder processing in whatever way it deems to be the most efficient for your current data distribution, on your current version of SQL Server. Default to CTEs.
But when SQL Server gets that process wrong, switch to temp tables.
To hear other peoples’ opinions on temp tables, check out the comments in this month’s T-SQL Tuesday invitation from Jeff Taylor. This month’s topic is temp tables, and as bloggers pour their hearts out, they’ll leave links over in the comments.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
One weird performance optimization for replacing CTEs with temp tables:
Using either select into (to construct the temp table as part of the insert) or “with(tablock)” can often be faster, even on an empty temp table. In principle there’s no reason for this to matter: SQL should know there’s no contention since it’s a private temp table. However, empirical testing showed a plain insert would perform noticeably slower, whereas these two alternatives were both faster (both approaches gave equal, faster performance).
Mind you, my notes on this issue are from 2018 and I don’t have sample code, so I don’t know if this idiosyncrasy remains an issue on newer editions. Personally, I always use select into when replacing CTEs, so this issue never comes up and thus I’ve never felt the need to re-visit it.
That’s from minimal logging
select into also got parallelism by default since some version of sql server
The write part of an INSERT INTO is always single threaded, even if the SELECT part was parallel.
When using and SELECT INTO the writes will happen in parallel too.
This would not helped in that specific example (because only a single row would be written into the #tmp) but when you are reading / writing >100k rows it can be a real perfomance boost.
But be aware that you shouldn’t use it in a procedure that is called 50 times per second, since creating / dropping the temp tables via SELECT INTO will cause some overhead, while a CREATE TABLE #tmp in a procedure will cache/reuse it (at the end of the procedure the #tmp will not really be dropped but just unassigned and reused at the next execution of that procedure)
I almost exclusively use temp tables in production code. CTEs are cool and lightweight but the performance trade offs are not worth it. The amount of times I have fixed a dev’s slow running query by converting it to a temp table is staggering.
Temp tables do have a small bit overhead on the writes but having CONSISTENT performance more than makes up for it. Also, if you need to use that dataset more than once, CTEs become expensive since the underlying query has to be ran again.
The only time I will use a CTE is for readability. If I have many simple subqueries, I will use CTEs so the code is easy to follow.
Interesting, but I disagree, and in my Mastering Query Tuning class, I show why CTEs are usually better by default. Feel free to check it out – I’d love to explain it but obviously it’s beyond what I can do in a comment. Hope that’s fair. Cheers!
I’ll be sure to check that out, thanks. I’ve ran many tests before which support my conclusion but SQL Server is always evolving. Might be worth a refresher.
Thank you for responding and for the great content.
My pleasure! Glad you enjoyed it.
Something interesting I tested: instead of separate indexes on Location and Reputation, I used a composite index:
CREATE INDEX Location_Reputation ON dbo.Users(Location, Reputation);
With this index, SQL Server only key-looked-up 250 rows, instead of all 2,758 users in that location (I’m testing on a smaller StackOverflowMini, vs. your 113,399 users on the full database).
My guess: since Reputation is already sorted within Location in this index, SQL Server doesn’t need to pull all matching rows and sort — it can walk the index in order and stop at 250. So the key lookup count matches the row goal instead of the whole location.
Learned this from your indexing class — thanks!
Glad you enjoyed the class! Yeah, when I write blog posts, I have to be really careful to scope how much I’m going to cover. Otherwise they turn into 10,000 word long monsters, heh. This one was just scoped to temp tables.