CTE vs Temp Table vs Subquery in SQL Server: Which Is Faster?

"Should I use a CTE or a temp table?" is one of the most common SQL Server questions, and the answer is often misunderstood. The key fact: a CTE is just a named query, not a stored result. Once you know that, choosing becomes straightforward.

CTE vs temp table vs subquery in SQL Server

The three options

-- 1. Subquery (derived table)
SELECT d.DepartmentId, d.TotalPay
FROM (SELECT DepartmentId, SUM(Amount) AS TotalPay
      FROM dbo.Payment GROUP BY DepartmentId) AS d
WHERE d.TotalPay > 100000;

-- 2. Common table expression (CTE)
WITH DeptPay AS (
    SELECT DepartmentId, SUM(Amount) AS TotalPay
    FROM dbo.Payment GROUP BY DepartmentId
)
SELECT DepartmentId, TotalPay
FROM DeptPay
WHERE TotalPay > 100000;

-- 3. Temporary table
SELECT DepartmentId, SUM(Amount) AS TotalPay
INTO #DeptPay
FROM dbo.Payment GROUP BY DepartmentId;

SELECT DepartmentId, TotalPay
FROM #DeptPay
WHERE TotalPay > 100000;

CTEs and subqueries perform the same

SQL Server expands a non-recursive CTE into the main query before optimizing, exactly as it does with a derived table. So the first two queries above normally get the same execution plan. A CTE is not faster or slower than a subquery; it is simply easier to read, especially when you chain several steps.

A CTE is not materialized

This is the part that catches people out. SQL Server does not store a CTE's result. If you reference the same CTE twice, the underlying query is usually evaluated twice:

WITH Expensive AS (
    SELECT EmployeeId, SUM(Amount) AS Total
    FROM dbo.Payment
    GROUP BY EmployeeId
)
SELECT a.EmployeeId, a.Total, b.Total AS OtherTotal
FROM Expensive a
JOIN Expensive b ON b.EmployeeId = a.EmployeeId + 1;   -- aggregation can run twice

If that inner query is expensive, a temp table that computes it once is often much faster.

When a temp table wins

  • The result is reused several times, in one query or across statements.
  • The query is very complex, with many joins. Breaking it into steps gives the optimizer smaller problems and better estimates.
  • You need statistics or an index on the intermediate result. Temp tables get statistics and can be indexed; CTEs cannot.
  • You want to debug step by step, by checking row counts in the middle.

The cost is writing the rows to tempdb, which is worth it for expensive intermediate results but wasteful for small, simple ones.

When a CTE or subquery wins

  • The intermediate result is used once.
  • The query is simple enough for the optimizer to handle in one go. Keeping it as one statement lets SQL Server push filters down and skip work that a temp table would have done in full.
  • You are inside a view or inline table-valued function, where temp tables are not allowed.

Recursive CTEs

CTEs have one unique feature: recursion, which is ideal for hierarchies such as managers and reports or account trees:

WITH OrgChart AS (
    SELECT EmployeeId, ManagerId, FullName, 0 AS Level
    FROM dbo.Employee
    WHERE ManagerId IS NULL                 -- the top of the tree
    UNION ALL
    SELECT e.EmployeeId, e.ManagerId, e.FullName, o.Level + 1
    FROM dbo.Employee e
    JOIN OrgChart o ON e.ManagerId = o.EmployeeId
)
SELECT * FROM OrgChart
OPTION (MAXRECURSION 200);

By default recursion stops with an error after 100 levels; MAXRECURSION changes the limit, and 0 removes it (use carefully).

How to decide

SituationUse
Result used once, simple queryCTE or subquery
Readability for multi-step logicCTE
Expensive result used more than onceTemp table
Very complex query with bad estimatesTemp table steps
Hierarchy or treeRecursive CTE
Inside a view or inline functionCTE or subquery

When in doubt, try both and compare actual plans and logical reads with realistic data.

Related reading:

Comments

Post a Comment

Popular posts from this blog

How to Read a SQL Server Execution Plan (Beginner's Guide)

Image
When a query is slow, the execution plan tells you exactly what SQL Server did to run it: which indexes it used, how it joined tables and where the time went. Plans can look intimidating, but you only need a few habits to find the problem in most of them. Estimated vs actual plans SQL Server Management Studio (SSMS) can show two kinds of plan: Estimated plan (Ctrl+L): what SQL Server plans to do, without running the query. Useful for queries that are too slow or too risky to run. Actual plan (Ctrl+M, then run the query): the same plan plus what really happened, including actual row counts and warnings. Use this whenever you can. Turn on IO and time statistics at the same time so you have numbers to compare after each change: SET STATISTICS IO, TIME ON; -- run the query with "Include Actual Execution Plan" enabled SET STATISTICS IO, TIME OFF; Read from right to left Data flows from right to left. The operators on the far right read data from tables and indexes; each operato...
Read more

SARGable Queries in SQL Server: Why Functions in WHERE Slow You Down

Image
You add the perfect index, run the query, and SQL Server still scans the whole table. One of the most common reasons is a non-SARGable predicate: a WHERE clause written in a way that stops SQL Server from using the index to seek. The fix is usually a small rewrite. What SARGable means SARGable stands for S earch ARG ument- able . A predicate is SARGable when SQL Server can use it to jump straight to the matching rows in an index (an index seek). If you wrap the indexed column in a function or calculation, SQL Server can no longer search the index for a value; it has to calculate the expression for every row, which means a scan. The rule of thumb: leave the column bare on one side of the comparison , and do any calculation on the other side. Common non-SARGable patterns and their fixes Functions on date columns -- Not SARGable: YEAR() runs on every row WHERE YEAR(OrderDate) = 2026 -- SARGable: a range on the bare column WHERE OrderDate >= '2026-01-01' AND OrderDate < ...
Read more

SQL Server Statistics Explained: Why Your Query Plans Go Wrong

Image
When a query suddenly picks a terrible plan, the root cause is very often statistics . SQL Server's optimizer does not look at your data when it builds a plan; it looks at statistics about your data. If those are missing or out of date, it guesses wrong, and a wrong guess can mean a scan instead of a seek, the wrong join type or a memory spill. What statistics are A statistics object describes how values are distributed in one or more columns. SQL Server creates one automatically for every index, and, with AUTO_CREATE_STATISTICS on, for columns you filter or join on. The optimizer uses them to estimate how many rows each step will return, and those estimates drive almost every plan choice. How to read a statistics object DBCC SHOW_STATISTICS ('dbo.PayrollLine', 'IX_PayrollLine_Run_PayCode'); The output has three parts: Header: when the statistics were last updated, how many rows the table had, and how many rows were sampled . Density vector: how unique the column...
Read more