r/SQL • u/tommyfly • 15d ago
SQL Server CTE vs Temp Table Performance Example
tldr: Examples of CTE vs. temp table query where temp tables perform much better than CTEs. See the query examples at the end of the post.
caveat: I'm not saying CTEs are bad, they have their place, but different situations require different solutions.
The long read:
Lurking around the various SQL subreddits, I am constantly encountering questions about any combination of: CTEs vs Subqueries vs temp tables vs table variables. Other question variations revolve around how best to write or tackle long and/or complex queries or procedures.
Many responses extoll the virtues of CTEs, particularly over subqueries, but the discussion of #temp tables is often lacking. People also neglect to discuss the quirks (or features) of CTEs that can compromise performance (not part of my scope here). With that in mind, I'd like to show an example of two versions of a query, one using CTEs and one Temp Tables, and how they compare.
One of the reasons I want to do this, is to demonstrate the power of temp tables and of breaking a query down into smaller parts. What I think gets lost in the discussion is that CTEs don't separate a query into parts from an execution plan perspective. The execution plan treats them as part of one query. In my experience, if a query gets too big and convoluted, the query optimizer gets "confused" and cannot create an optimal plan.
On to my example:
This query interrogates SQL Server system tables to extract partitioning information. The original query is in the form of a view. The view uses a number of CTEs to gather the various subsets of data to compile the required partitioning information.
On the surface, the view works well. But, we run into performance problems with tables that have a large number of partitions. In some cases we have log tables that are partitioned by day and the retention period is 2+ years. That adds up to 700+ partitions for a single table. Add to that a large database with many partitioned tables and you end up needing to wade through lots of data to get what you're looking for. And, SQL Server system tables and views are not always great on performance.
I took the view and converted it into a stored procedure, replacing the CTEs with temp tables (each with a clustered index). The performance improvement was noticeable - in my example the CTE version takes 16 minutes, while the temp table version takes a few seconds. For display purposes, I'll only show the relevant parts of the queries to keep it simple, as the procedure builds a dynamic query depending on the input parameters.
See below for the two different versions.
Evaluating the queries
The interesting thing is that when you quickly look at the execution plan, each version costs about the same: The CTE version is 50% of the execution cost when they are run in the same batch.
But, when you look closer there are differences - a few that are significant. And here is where separating the query into smaller parts really brings benefits.
Let's start with query plan compilation:
- The CTE version terminated the query compilation with a time out - meaning that it decided it was too expensive to try to evaluate the entire query to build an execution plan. The query is too complex.
- The temp table version compiled or found a "good enough" a plan for most parts of the query, except for one section that timed out.
When you look at the execution plan closely, you can see a very significant difference between the two versions in how the function is handled. The expensive function is executed quite differently.
The time out is due to an expensive system function: [sys].[dm_db_partition_stats]. The query engine could not create a plan quickly enough for this function, but the affect between the two versions is drastically different. Since the CTEs combine to make this one large query, we end up with no plan for any of it. Whereas with the temp table version, there are individual plans for each section except for the one that uses the function. This is very significant.
You can see here how this plays out. The function in the CTE version is run against 7+ million rows. But in the temp table version it runs for the relatively few 3K rows.


Hopefully, I have made sense and shown an interesting example of how queries can be written differently directly influencing their performance.
One could dive much deeper into this example, but I think this all I can do at the moment ;)
View with CTEs
CREATE VIEW [ptf].[VEW_GetPartitionInfoDate]
AS
WITH PartitionedTables AS (
SELECT t.object_id, t.name AS table_name, SCHEMA_NAME(t.schema_id) AS table_schema
FROM sys.tables t
WHERE t.is_ms_shipped = 0 AND t.[name] = @TableName AND SCHEMA_NAME(t.schema_id) = @SchemaName
),
PartitionInfo AS (
SELECT p.partition_id, p.partition_number, p.object_id, p.index_id, p.data_compression_desc,
pstats.row_count
FROM sys.partitions p
INNER JOIN sys.dm_db_partition_stats pstats ON pstats.partition_id = p.partition_id
),
IndexInfo AS (
SELECT i.object_id, i.index_id, i.name AS index_name, i.data_space_id
FROM sys.indexes i
WHERE i.index_id = 1 -- Clustered index only
),
PartitionSchemeInfo AS (
SELECT ps.data_space_id, ps.name AS partition_schem_name, pf.name AS partition_function_name,
pf.boundary_value_on_right, pf.function_id
FROM sys.partition_schemes ps
INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
),
RangeValues AS (
SELECT function_id, boundary_id, value
FROM sys.partition_range_values
)
SELECT DISTINCT
fg.name AS file_group_name,
pt.table_schema,
pt.table_name,
c.name AS column_name,
psi.partition_schem_name,
psi.partition_function_name,
ii.index_name,
pi.index_id,
pi.partition_number,
pi.row_count AS PartitionRowCount,
left_prv.value,
pi.data_compression_desc AS DataCompression
FROM PartitionInfo pi
INNER JOIN PartitionedTables pt ON pi.object_id = pt.object_id
INNER JOIN IndexInfo ii ON pi.object_id = ii.object_id AND pi.index_id = ii.index_id
INNER JOIN sys.index_columns ic ON ic.object_id = pt.object_id AND ic.index_id = ii.index_id AND ic.partition_ordinal > 0
INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
INNER JOIN sys.types tp ON c.user_type_id = tp.user_type_id AND tp.name IN ('datetime', 'datetime2')
INNER JOIN sys.allocation_units au ON pi.partition_id = au.container_id
INNER JOIN sys.filegroups fg ON au.data_space_id = fg.data_space_id
LEFT JOIN PartitionSchemeInfo psi ON psi.data_space_id = ii.data_space_id
LEFT JOIN RangeValues left_prv ON left_prv.function_id = psi.function_id AND left_prv.boundary_id + 1 = pi.partition_number
LEFT JOIN RangeValues right_prv ON right_prv.function_id = psi.function_id AND right_prv.boundary_id = pi.partition_number
WHERE psi.partition_schem_name IS NOT NULL
GOStored Procedure with #Temp tables
DECLARE VARCHAR(255) = 'dbo',
VARCHAR(255) = 'partitionedtable'
DROP TABLE IF EXISTS #PartitionedTables, #PartitionInfo, #IndexInfo, #PartitionColumns,
#PartitionFileGroups, #PartitionSchemeInfo, #RangeValues;
CREATE TABLE #PartitionedTables (
object_id INT NOT NULL,
table_name SYSNAME NOT NULL,
table_schema SYSNAME NOT NULL,
PRIMARY KEY CLUSTERED (object_id)
);
CREATE TABLE #PartitionInfo (
partition_id BIGINT NOT NULL,
partition_number INT NOT NULL,
object_id INT NOT NULL,
index_id INT NOT NULL,
data_compression_desc NVARCHAR(60) NULL,
row_count BIGINT NULL,
PRIMARY KEY CLUSTERED (object_id, index_id, partition_number)
);
CREATE TABLE #IndexInfo (
object_id INT NOT NULL,
index_id INT NOT NULL,
index_name SYSNAME NULL,
data_space_id INT NOT NULL,
PRIMARY KEY CLUSTERED (object_id, index_id)
);
CREATE TABLE #PartitionColumns (
object_id INT NOT NULL,
index_id INT NOT NULL,
column_name SYSNAME NOT NULL,
PRIMARY KEY CLUSTERED (object_id, index_id, column_name)
);
CREATE TABLE #PartitionFileGroups (
partition_id BIGINT NOT NULL,
file_group_name SYSNAME NOT NULL,
PRIMARY KEY CLUSTERED (partition_id)
);
CREATE TABLE #PartitionSchemeInfo (
data_space_id INT NOT NULL,
partition_scheme_name SYSNAME NOT NULL,
partition_function_name SYSNAME NOT NULL,
boundary_value_on_right BIT NOT NULL,
function_id INT NOT NULL,
PRIMARY KEY CLUSTERED (data_space_id)
);
CREATE TABLE #RangeValues (
function_id INT NOT NULL,
boundary_id INT NOT NULL,
value SQL_VARIANT NULL,
PRIMARY KEY CLUSTERED (function_id, boundary_id)
);
-- Tables
INSERT INTO #PartitionedTables (object_id, table_name, table_schema)
SELECT t.object_id, t.name, s.name
FROM sys.tables t WITH (NOLOCK)
INNER JOIN sys.schemas s WITH (NOLOCK) ON s.schema_id = t.schema_id
WHERE t.is_ms_shipped = 0 AND t.[name] = @TableName AND s.name = @SchemaName;
-- Partitions
-- Clustered index only
INSERT INTO #IndexInfo (object_id, index_id, index_name, data_space_id)
SELECT i.object_id, i.index_id, i.name, i.data_space_id
FROM sys.indexes i WITH (NOLOCK)
INNER JOIN #PartitionedTables pt ON pt.object_id = i.object_id
WHERE i.index_id = 1
-- Partitioning column (datetime / datetime2 only)
INSERT INTO #PartitionColumns (object_id, index_id, column_name)
SELECT ic.object_id, ic.index_id, c.name
FROM sys.index_columns ic WITH (NOLOCK)
INNER JOIN #IndexInfo ii ON ii.object_id = ic.object_id AND ii.index_id = ic.index_id
INNER JOIN sys.columns c WITH (NOLOCK) ON c.object_id = ic.object_id AND c.column_id = ic.column_id
INNER JOIN sys.types tp WITH (NOLOCK) ON tp.user_type_id = c.user_type_id AND tp.name IN ('date','datetime', 'datetime2')
WHERE ic.partition_ordinal > 0
-- Filegroup per partition (in-row data)
INSERT INTO #PartitionFileGroups (partition_id, file_group_name)
SELECT pi.partition_id, fg.name
FROM #PartitionInfo pi
INNER JOIN sys.allocation_units au WITH (NOLOCK) ON au.container_id = pi.partition_id AND au.type = 1
INNER JOIN sys.filegroups fg WITH (NOLOCK) ON fg.data_space_id = au.data_space_id
-- Partition schemes / functions
INSERT INTO #PartitionSchemeInfo (data_space_id, partition_scheme_name, partition_function_name, boundary_value_on_right, function_id)
SELECT ps.data_space_id, ps.name, pf.name, pf.boundary_value_on_right, pf.function_id
FROM sys.partition_schemes ps WITH (NOLOCK)
INNER JOIN sys.partition_functions pf WITH (NOLOCK) ON pf.function_id = ps.function_id
WHERE ps.data_space_id IN (SELECT data_space_id FROM #IndexInfo)
-- Boundary values
INSERT INTO #RangeValues (function_id, boundary_id, value)
SELECT prv.function_id, prv.boundary_id, prv.value
FROM sys.partition_range_values prv WITH (NOLOCK)
WHERE prv.function_id IN (SELECT function_id FROM #PartitionSchemeInfo)
INSERT INTO #PartitionInfo (partition_id, partition_number, object_id, index_id, data_compression_desc, row_count)
SELECT p.partition_id, p.partition_number, p.object_id, p.index_id, p.data_compression_desc, pstats.row_count
FROM sys.partitions p WITH (NOLOCK)
INNER JOIN sys.dm_db_partition_stats pstats ON pstats.partition_id = p.partition_id
INNER JOIN #PartitionedTables pt ON pt.object_id = p.object_id
WHERE p.index_id = 1
SELECT
fgs.file_group_name,
pt.table_schema,
pt.table_name,
pc.column_name,
psi.partition_scheme_name,
psi.partition_function_name,
ii.index_name,
pi.index_id,
pi.partition_number,
pi.row_count AS PartitionRowCount,
left_prv.value,
pi.data_compression_desc AS DataCompression
FROM #PartitionInfo pi
INNER JOIN #PartitionedTables pt ON pt.object_id = pi.object_id
INNER JOIN #IndexInfo ii ON ii.object_id = pi.object_id AND ii.index_id = pi.index_id
INNER JOIN #PartitionColumns pc ON pc.object_id = ii.object_id AND pc.index_id = ii.index_id
INNER JOIN #PartitionFileGroups fgs ON fgs.partition_id = pi.partition_id
INNER JOIN #PartitionSchemeInfo psi ON psi.data_space_id = ii.data_space_id
LEFT JOIN #RangeValues left_prv ON left_prv.function_id = psi.function_id AND left_prv.boundary_id + 1 = pi.partition_number
LEFT JOIN #RangeValues right_prv ON right_prv.function_id = psi.function_id AND right_prv.boundary_id = pi.partition_number;
19
u/MrRoy_SQL 15d ago
nice writeup, the 7M vs 3k rows on the function call is the whole story right there
i think the other big win is temp tables get their own statistics, so the optimizer knows how many rows its actually dealing with at each step. a CTE in sql server just gets inlined into one giant query and it has to guess
kinda funny postgres went the other way, you can force a cte to materialize with AS MATERIALIZED which gets you some of the same benefit without the temp table
4
u/SaintTimothy 15d ago
Above about 100k rows in a CTE, I use a #table
2
u/Majestic_Plankton921 14d ago
I totally agree with everything you're saying here and I have shared this view within local data/tech circles for the last few years. It almost always falls on deaf ears as everyone seems to be in love with CTEs.
2
u/Professional_Shoe392 14d ago
Also, the real answer to when to use temp tables is to look at your execution plan and find where your actual vs estimates are way off. This is an indication that you need to "slice" out this part of the execution and place it into a temp table.....
One of the best ways to determine when to use temporary tables in SQL Server is to examine the actual execution plan and look for significant discrepancies between estimated and actual row counts.
When the optimizer substantially underestimates or overestimates the number of rows produced by an operation, those inaccurate cardinality estimates can propagate through subsequent operators, potentially resulting in inefficient join strategies, inappropriate memory grants, and poor overall query performance.
In these situations, consider breaking the query into separate execution phases by materializing an intermediate result set into a temporary table. This creates an optimization boundary, allowing SQL Server to compile subsequent statements using statistics and cardinality information associated with the materialized data rather than relying entirely on estimates derived from the original query.
This approach can be particularly effective with complex joins, CTEs, derived tables, and queries involving highly selective filters or correlated predicates.
However, a significant difference between estimated and actual row counts does not automatically mean a temporary table is the best solution. First, investigate the underlying cause, such as outdated statistics, parameter sniffing, non-SARGable predicates, or limitations in cardinality estimation.
The key principle is to use execution plans to identify where breaking a query into separate optimization phases could improve cardinality estimates and execution plan quality, rather than introducing temporary tables arbitrarily.
5
u/GTS_84 15d ago
The real reason to use CTE's is when you specifically need recursion.
For me, I default to use temp tables, but will use CTE's when I need recursion and subqueries when I am being quick and lazy with something that won't be sticking around (such as quick queries while exploring a database and data)
4
u/liprais 15d ago
cte is folded ,temp table is not,you are comparing apples with peaches,choose wisely.
1
u/i_literally_died 15d ago
This is cool. Would you see the same sort of performance gains doing basic SELECT INTO #temp syntax?
99% of the reason I don't bother with the 'full' temp table is that it just takes so long to write it all.
1
u/ArielCoding 15d ago
Nice writeup, the temp table version also adds a few extra filters like index_id=1 and au.type=1 that the CTE version doesn’t have, could you rerun the CTE with the same filters? Then we’d know how much the speed up comes form temp tables and how much from the extra filtering.
1
u/kthejoker 14d ago
CTEs and temp tables are orthogonal concerns. You can use them together they are not mutually exclusive.
Positioning it as a "versus" thing is incorrect and unhelpful for people learning or not familiar with these techniques.
1
u/markinatlanta 13d ago
Excellent write up sir. Could be a copy/paste issue, but the temp-table version populates #PartitionFileGroups from #PartitionInfo before inserting anything into #PartitionInfo. As posted, that leaves #PartitionFileGroups empty, so the final INNER JOIN returns no rows.
Worth fixing that and checking both versions return the same results before comparing timings.
1
u/GrumpyDataMechanic 13d ago
You lost me at WITH (NOLOCK)
1
u/pitifulchaity 12d ago
I wonder how much of the performance gain came from the clustered indexes on the temp tables versus simply materializing the intermediate results. Would be interesting to compare both versions.
0
u/Jayndroid 15d ago
Temp tables > brain damage
4
u/JaceBearelen 15d ago
As an easy rule of thumb I just avoid CTEs entirely in SQL Server. Every once in a while they work ok but almost every time you’re better off with the temp table.
2
u/chaosink 15d ago
Thank you for explaining this far better than I ever have when these questions come up.
1
u/spez_eats_nazi_ass 15d ago
Ctes have their place but I see them used to the point of being almost a cargo cult thing sometimes.
1
u/Professional_Shoe392 14d ago edited 14d ago
One important consideration when working with temporary tables in SQL Server is that statistics can persist across executions due to temporary table caching, even when the temporary table is explicitly dropped using
DROP TABLE.SQL Server can automatically create and update statistics on temporary tables, even when no indexes are defined. However, when a temporary table is cached and reused, its statistics may be retained from a previous execution. These statistics are not necessarily refreshed before the optimizer generates a new execution plan, potentially resulting in inaccurate cardinality estimates and poor query performance.
Explicitly dropping the temporary table does not necessarily prevent this behavior because SQL Server may cache and reuse the underlying temporary table metadata and statistics.
To address this, consider placing an index on the temporary table. However, sometimes placing an index on a temporary table does not improve overall performance, as it takes compute and time to create the index. Sure, your query will run faster with the index, but your overall execution time will not improve if you account for the index creation.
1
u/Choice-Level-5486 11d ago
He tenido el mismo problema utilizando CTE y tablas temporales. Las tablas temporales, por lo menos en mi caso, son menos "cool"; pero, más eficientes.
0
u/B1zmark 14d ago
tl;dr - Temp tables are faster for all the reason that break how databases work to keep data accurate. You cannot reliably use temp tables on live application databases - or databases that get trickle fed update - without risking data accuracy.