No, SQL Server CTEs do not store results

There’s this weird misconception floating around LinkedIn and reddit that SQL Server CTEs somehow store results in either memory or tempdb.

This is wrong and whoever states that CTEs store results either have no idea what they’re talking about or are intentionally trying to mislead people for engagement farming.

What is a CTE?

A simple way to look at common table expressions is to think of them as shortcuts to queries.

Here’s an example:

The query defined inside a CTE is not stored anywhere in the database, unlike a view.
It’s also limited to the scope of the current SELECT/INSERT/UPDATE/DELETE/MERGE statement.

This means that the following example will fail at the second SELECT statement:

Error message:

Msg 208, Level 16, State 1, Line 16
Invalid object name ‘top_10_users_rep’.

I didn’t even terminate the queries with semicolons so that you won’t be tempted to say that that’s what breaks the second SELECT.

What better way to make a point than with an actual demo

Cláudio Silva already demonstrates this in one of his blog posts.
But some people might be tempted to argue that NEWID() isn’t deterministic, so that’s why it’s evaluated every time.

For the demo I’m using the 180GB StackOverflow database on SQL Server 2022 CU20 Developer Edition.

First, I create this index to make things easier:

For later use, I’ll get the size of this index in 8KB pages using an index visualization query (this is a query that can be fully satisfied from the index itself, without having to touch the table).

The IO statistics look like this:

(8917507 rows affected)
Table ‘Users’. Scan count 1, logical reads 15497, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

So the index stores 8917507 rows in 15497 8KB pages.
Doing the math (15497*8/1024.), I get 121MB.

One other way to get index details (but not the page count) for a single table is via Brent Ozar’s sp_BlitzIndex.

CTE query and execution info

My demo query (derived from this query):

The execution plan looks like this:

Notice how for each ID we have the same access pattern against the Users table (specifically the IX_Reputation_Id NC index).

At this point you might be asking yourself how I can tell which section matches which ID.
Hovering over the Filter operator will show which ID it’s filtering for.

Moving on to the IO statistics output.

(3 rows affected)
Table ‘Users’. Scan count 30, logical reads 50037, physical reads 0, page server reads 0, read-ahead reads 209, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

The relevant parts hare are:

• No data is being read from some worktable stored in tempdb or anywhere else
• The query requires the database engine to read a total of 50037 8KB pages (390.9MB).

Doing the math

So where does that number (50037) come from?

Well, let’s deconstruct the CTEs and see what IO statistics we get for the queries.

Rankings CTE

This CTE is the responsible for the Index Scan operators seen in the execution plan.

Running the query used for the Rankings CTE with SET STATISTICS IO ON, gives us the following info:

(8917507 rows affected)
Table ‘Users’. Scan count 1, logical reads 15497, physical reads 0, page server reads 0, read-ahead reads 14459, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

If that number (15497) of 8KB pages seems familiar, it’s because that’s the same obtained from the index visualization query.
This is no surprise since the query:

1. Reads all the rows.
2. Only needs to use the IX_Reputation_Id index since the two columns it requires are part of said index.

Counts CTE

This CTE is responsible for the Index Seek operators.

And get the following IO statistics:

(1 row affected)
Table ‘Users’. Scan count 1, logical reads 1153, physical reads 0, page server reads 0, read-ahead reads 1158, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Since this query only has to read 650065 records from the IX_Reputation_Id index, the amount of 8KB pages read by the database engine is noticeably lower – 1153.

Putting it all together

As the execution plan shows, the database engine repeats the same access pattern for each of the 3 IDs.

Meaning that the math would look like this:

Yeah, I know it’s not an exact match (an 87 page difference), but it’s close enough to prove the point here.

So, both the execution plan and the IO statistics confirm that there’s no magical place where CTEs store their data, and that it’s being pulled each time from the table/index.

Conclusion

No, SQL Server CTEs do not store or materialize the result anywhere, and luckily, the official documentation also got updated to reflect this (Thanks, Erik Darling!).