T-SQL & Development

When CTEs Don’t Work, Try Temp Tables – #TSQL2sday

By Brent Ozar · August 11, 2026 · no comments

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.

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;

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:

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;

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.

When CTEs Don’t Work, Try Temp Tables – #TSQL2sday

Get my new posts by email

Keep digging

Leave a comment Cancel reply