This month Jeff Taylor is hosting T-SQL Tuesday #201, and he came out swinging. His position, in his words: "reaching for a temp table should be the exception, not the reflex." His evidence is a client proc that pulled 250+ million rows into a #temp table to answer a one-row question, and his rewrite that did the same work against the existing indexes.
He's right about that proc. And I believe he's also right about the general shape of the problem. I've pulled that same pattern out of enough procedures to know it's real.
But he invited disagreement, and I have some. So I built a lab to find the case his argument doesn't cover.
The Claim I'm Testing
Jeff's argument rests on one idea: the base table already has the indexes and the statistics, so copying the data into tempdb is redundant work. Same data, worse container.
My counter is that the temp table isn't just a container. It's a plan boundary.
SQL Server optimizes one statement at a time. A CTE isn't a statement. It's a named subquery the Optimizer expands inline at every reference, so all of it compiles and executes as one plan. Writing to a #temp table ends the statement. That hard stop changes two things: how many times the same work gets done, and how big any single plan is allowed to get -- and neither of these has anything to do with where the rows are sitting. The second one matters more than it sounds like, because the decision to go parallel, and the DOP that comes with it, are made once for the whole statement.
So I designed a demo to be maximally unfair to myself. The base table gets a perfect covering index and full-scan statistics update. Everything Jeff says the Optimizer already has, it has. If the temp table still wins from there, it wins on something else entirely.
My evidence method for this post is logical reads. That's the number that carries the argument. Everything else in here is an observation.
The Lab
SQL Server 2025 RTM CU7, build 17.0.4065.4, Cardinality Estimation model 170. Same laptop as always -- 14 CPUs, 16GB RAM with 12GB allocated to SQL Server, NVMe storage. The demo code itself can be run in SQL Server 2016 and later.
Two tables, 50,000 customers and 2,000,000 orders. Row caps are literals in the TOP so the script can't run away from you.
USE master;
GO
CREATE DATABASE TempTableDemo;
GO
ALTER DATABASE TempTableDemo SET RECOVERY SIMPLE;
GO
USE TempTableDemo;
GO
/* child table first, then parent */
IF OBJECT_ID('dbo.tblOrders','U') IS NOT NULL DROP TABLE dbo.tblOrders;
IF OBJECT_ID('dbo.tblCustomers','U') IS NOT NULL DROP TABLE dbo.tblCustomers;
GO
CREATE TABLE dbo.tblCustomers
(
CustomerID INT NOT NULL,
CustomerName VARCHAR(100) NOT NULL,
Region VARCHAR(20) NOT NULL,
CONSTRAINT pkcl_tblCustomers_CustomerID PRIMARY KEY CLUSTERED (CustomerID)
);
GO
CREATE TABLE dbo.tblOrders
(
OrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATE NOT NULL,
OrderTotal DECIMAL(10,2) NOT NULL,
CONSTRAINT pkcl_tblOrders_OrderID PRIMARY KEY CLUSTERED (OrderID)
);
GO
/* 50,000 customers. hard cap in TOP. */
;WITH Tally AS
(
SELECT TOP (50000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
INSERT dbo.tblCustomers (CustomerID, CustomerName, Region)
SELECT n,
'Customer ' + CAST(n AS VARCHAR(10)),
CASE n % 4
WHEN 0 THEN 'North'
WHEN 1 THEN 'South'
WHEN 2 THEN 'East'
ELSE 'West'
END
FROM Tally;
GO
/* 2,000,000 orders. hard cap in TOP.
Multiplier 7919 is prime -- spreads orders across customers evenly. */
;WITH Tally AS
(
SELECT TOP (2000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
INSERT dbo.tblOrders (OrderID, CustomerID, OrderDate, OrderTotal)
SELECT n,
((n * 7919) % 50000) + 1,
DATEADD(DAY, -(n % 1095), CAST('2026-08-11' AS DATE)),
CAST(((n % 500) + 10) AS DECIMAL(10,2))
FROM Tally;
GO
/* Give the base table exactly what Jeff argues it already has:
the right covering index and clean statistics. Nothing to blame later. */
CREATE NONCLUSTERED INDEX idx_tblOrders_CustomerID_OrderDate
ON dbo.tblOrders (CustomerID, OrderDate)
INCLUDE (OrderTotal);
GO
UPDATE STATISTICS dbo.tblOrders WITH FULLSCAN;
UPDATE STATISTICS dbo.tblCustomers WITH FULLSCAN;
GO
The Question
Top 100 customers by trailing-twelve-month spend, restricted to those above the average, displayed next to the population average and maximum.
Nothing exotic. This is a typical dashboard query. The thing to notice is that it needs the same aggregate four separate times -- once to list, once for the average column, once for the maximum column, and once more in the WHERE clause.
Round 1: No Temp Table
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
/* lab server only -- don't run this on anything shared */
DBCC FREEPROCCACHE;
GO
;WITH CustomerTotals AS
(
SELECT o.CustomerID,
SUM(o.OrderTotal) AS TotalSpend,
COUNT_BIG(*) AS OrderCount
FROM dbo.tblOrders o
WHERE o.OrderDate >= '2025-08-11'
GROUP BY o.CustomerID
)
SELECT TOP (100)
c.CustomerName,
ct.TotalSpend,
ct.OrderCount,
(SELECT AVG(x.TotalSpend) FROM CustomerTotals AS x) AS AvgSpendAllCustomers,
(SELECT MAX(y.TotalSpend) FROM CustomerTotals AS y) AS MaxSpendAllCustomers
FROM CustomerTotals ct JOIN dbo.tblCustomers c
ON c.CustomerID = ct.CustomerID
WHERE ct.TotalSpend > (SELECT AVG(z.TotalSpend) FROM CustomerTotals AS z)
ORDER BY ct.TotalSpend DESC;
GO
Here's the return:
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 2 ms. DBCC execution completed. If DBCC printed error messages, contact your system administrator. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 39 ms. SQL Server parse and compile time: CPU time = 529 ms, elapsed time = 529 ms. (100 rows affected) Table 'tblOrders'. Scan count 60, logical reads 26328, 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. Table 'Workfile'. 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 'tblCustomers'. Scan count 0, logical reads 32228, physical reads 0, page server reads 0, read-ahead reads 7, 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. SQL Server Execution Times: CPU time = 2203 ms, elapsed time = 309 ms. Completion time: 2026-08-10T10:03:58.1428260-05:00
Note the compile. 529 ms elapsed to build the plan against 309 ms elapsed to run it. This query spent longer being planned than being executed. On CPU it goes the other way, since execution burned 2,203 ms across 14 threads, but on the wall clock the compile won.
Round 2: The Temp Table
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
CREATE TABLE #CustomerTotals
(
CustomerID INT NOT NULL PRIMARY KEY CLUSTERED,
TotalSpend DECIMAL(19,2) NOT NULL,
OrderCount BIGINT NOT NULL
);
/* one pass over the base table. that is the whole argument. */
INSERT #CustomerTotals (CustomerID, TotalSpend, OrderCount)
SELECT o.CustomerID,
SUM(o.OrderTotal),
COUNT_BIG(*)
FROM dbo.tblOrders o
WHERE o.OrderDate >= '2025-08-11'
GROUP BY o.CustomerID;
DECLARE @AvgSpend DECIMAL(19,2);
DECLARE @MaxSpend DECIMAL(19,2);
SELECT @AvgSpend = AVG(TotalSpend),
@MaxSpend = MAX(TotalSpend)
FROM #CustomerTotals;
SELECT TOP (100)
c.CustomerName,
ct.TotalSpend,
ct.OrderCount,
@AvgSpend AS AvgSpendAllCustomers,
@MaxSpend AS MaxSpendAllCustomers
FROM #CustomerTotals ct JOIN dbo.tblCustomers c
ON c.CustomerID = ct.CustomerID
WHERE ct.TotalSpend > @AvgSpend
ORDER BY ct.TotalSpend DESC;
GO
Five sets of timings come back, but only three statements touch a table:
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. SQL Server parse and compile time: CPU time = 6 ms, elapsed time = 6 ms. Table 'tblOrders'. Scan count 15, logical reads 6582, physical reads 1, page server reads 0, read-ahead reads 6547, 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 'Workfile'. 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. SQL Server Execution Times: CPU time = 420 ms, elapsed time = 127 ms. (50000 rows affected) SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 3 ms. Table '#CustomerTotals_____________________________________________________________________________________________________000000000033'. Scan count 1, logical reads 187, 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. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 26 ms. SQL Server parse and compile time: CPU time = 31 ms, elapsed time = 37 ms. (100 rows affected) Table 'tblCustomers'. Scan count 0, logical reads 328, physical reads 1, page server reads 0, read-ahead reads 240, 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 '#CustomerTotals_____________________________________________________________________________________________________000000000033'. Scan count 1, logical reads 187, 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. SQL Server Execution Times: CPU time = 47 ms, elapsed time = 48 ms. Completion time: 2026-08-10T10:04:49.4151381-05:00
The Scoreboard
| Measurement | Round 1 (no temp table) |
Round 2 (#temp) |
Ratio |
|---|---|---|---|
| tblOrders logical reads | 26,328 | 6,582 | 4.00x |
| tblCustomers logical reads | 32,228 | 328 | 98.3x |
| #CustomerTotals logical reads | n/a | 374 (187 x 2) |
-- |
| Worktable / Workfile | 0 | 0 | -- |
| Total logical reads | 58,556 | 7,284 | 8.04x |
| CPU time | 2,203 ms | 483 ms (summed) |
4.56x |
| Elapsed time | 309 ms | 202 ms (summed) |
1.53x |
Eight times the reads to produce the same hundred rows, on a base table with the ideal index and freshly rebuilt statistics.
Three things in the plans explain that gap. One I expected, one I didn't, and one I had completely backwards.
Four Scans, Not One
Round 1's execution plan contains four separate Index Scan operators against tblOrders. All four hit idx_tblOrders_CustomerID_OrderDate. All four report 14 actual executions. All four read 668,681 rows.
Round 2's INSERT contains one. Same index, same 14 executions, same 668,681 rows.
26,328 divided by 6,582 is 4.0000. Not approximately four. Exactly four. Scan count agrees independently: 60 against 15.
This is the first mechanic. A CTE is a named subquery, not a stored result -- the Optimizer expands its definition inline everywhere you reference it, and nothing requires it to notice that all four copies would return the same rows. So it built all four and ran all four. The temp table built the answer once and read it back twice at 187 logical reads a pop, because the AVG and the MAX came out of a single scan.
And before anyone asks about the spool
The Optimizer has an out here. When it spots the same subtree repeated in a plan, it can run that work once, park the result in a worktable in tempdb, and have the other references read from it -- effectively building itself a temp table because I didn't. If that had happened, the four scans would have collapsed to one and there'd be no post.
It didn't happen. Neither plan contains a Spool operator of any kind. The STATISTICS IO output says the same thing from the other direction: Worktable and Workfile both sit at 0 logical reads in Round 1, which is exactly where a spool would have shown up.
The 98x I Didn't Predict
The tblCustomers number is bigger than the tblOrders number, and it has nothing to do with the four scans.
Both rounds seek tblCustomers. Same clustered index, same operator, scan count 0 on both. The difference is how many times:
| Round | Seek on pkcl_tblCustomers_CustomerID |
Actual executions |
Logical reads |
|---|---|---|---|
| Round 1 | Clustered Index Seek | 15,568 | 32,228 |
| Round 2 | Clustered Index Seek | 100 | 328 |
15,568 seeks against 100, to return the same hundred rows.
The cause is parallelism. Round 1 ran at DOP 14 with the TOP sitting above the Gather Streams operator. Each of the 14 threads sorted its own slice of the above-average customers and fed seeks into tblCustomers on demand, and 15,568 of those seeks went through before the TOP 100 was finally satisfied.
Round 2's final statement ran serial. 113 rows out of the Sort, 100 seeks. Done.
That difference is structural, and it's the part of this that I think gets overlooked. Look at the estimated subtree costs:
| Statement | Est. subtree cost | DOP |
|---|---|---|
| Round 1, entire query | 24.32 | 14 |
| Round 2, INSERT | 7.95 | 14 |
| Round 2, AVG and MAX | 0.23 | 1 |
| Round 2, final SELECT | 1.46 | 1 |
The temp table didn't just avoid three extra scans. It broke one expensive parallel statement into three cheap ones, and each of the three got its own plan. The INSERT still went parallel at DOP 14. The other two ran serial. Same plan shape as Round 1 -- Top, then Nested Loops, then Sort -- but one thread instead of 14, and it stops at 100.
The Part That Proved Me Wrong
Going in, my working theory was that the temp table would win because materializing gives the Optimizer a real row count where a chained estimate had degraded into a guess. The estimation argument. It's the one I've made verbally many times before.
The plans say otherwise. Compare the estimate to the actual for the above-average customer set, which is the row count that drives everything downstream:
| Round | Estimated | Actual | Miss |
|---|---|---|---|
| Round 1 (nested loops join) | 24,997.4 | 24,994 | 0.01% |
| Round 2 (scan of #CustomerTotals) | 15,000 | 24,994 | 40% low |
Round 1's estimate was nearly perfect. Round 2's was exactly 30 percent of the 50,000 rows in the temp table -- SQL Server's fallback for an inequality when it can't know the value at compile time, which it can't, because @AvgSpend is a local variable.
The temp table version won by 8x on logical reads while carrying the worse cardinality estimate.
Better information did not produce the better plan. That kills the theory I started with, and it narrows what's left. The temp table won on the two things I described up top -- work that wasn't repeated, and plans small enough to stay serial. Nothing about the quality of the estimates, and nothing about where the rows were sitting.
Why Elapsed Time Will Lie to You
Reads went down 8x. The stopwatch went down 1.53x. Those two numbers describe the same pair of queries, and if you only look at one of them you'll reach the wrong conclusion.
Round 1 burned 2,203 ms of CPU to finish in 309 ms. That's 14 threads on an idle laptop, and it's the whole illusion. The work didn't shrink. It got spread across cores that had nothing else to do. A developer clocks both versions, sees 309 against 202, and decides the temp table isn't worth the bother. On an idle box that's a fair reading of the stopwatch.
Run it a thousand times a day on a production server and those cores are already busy. The reads don't care how many threads you have. Round 1 is still asking for eight times the pages and four and a half times the CPU to produce the same hundred rows, and the parallelism that made it look fast is the first thing to disappear under load.
Worth noting Round 2 won from behind. tblOrders in Round 1 shows 0 physical reads and 0 read-ahead -- fully cached. Round 2 shows 1 physical read and 6,547 read-ahead reads, meaning it was pulling pages off disk. It still won by 8x.
CPU and elapsed time are observations here, not evidence. The reads are the argument, but the gap between them is what gets temp tables ripped out of working code.
So, Friend or Foe?
Everything Jeff says the Optimizer already has, it had. The index was there and it was used. The estimates were better without the temp table. And the version without still did four times the reads on tblOrders, 98 times the reads on tblCustomers, and four and a half times the CPU.
Which supports what I'm saying about the temp table's value not being about where the rows live. The real value is that a temp table is a plan boundary. Everything else is a side effect.
Materializing forced the engine to solve three small problems instead of one big one. The aggregate got computed once instead of four times. The final SELECT ran serially, which is what collapsed 15,568 seeks down to 100. None of that required a single byte of the data to be sitting somewhere else.
So, my rule of thumb for when do I reach for a #temp table or not, and how do I decide?
I always have 3 questions: How many times will the result be read? How many times will it be changed? And how much of it am I moving to find out?
Read more than once? Materialize it. That's this demo. Four references, four evaluations, and no spool coming to rescue you. The break-even is lower than people think, and two references is often enough, because you're trading one write for one whole extra pass.
Changed more than once? Materialize it. If you're updating a working set in stages -- flag these rows, then recalculate those, then filter on what's left -- a CTE can't hold that for you. You can modify data through a CTE, but the CTE only exists for the one statement it's attached to, so nothing carries forward to the next step. Each stage rebuilds the set from scratch. This is the case Jeff's argument doesn't reach, because you're not copying data to search it. You're copying it to work on it.
One statement grown big enough that the plan is the problem? Split it. A 24-cost plan making a single parallelism decision for the whole tree can be worse than three small plans each making their own. This is the one nobody looks for, and it's what produced the 98x above.
Reading it once, start to finish? Leave it alone. This is Jeff's 250 million rows and he's right. One reference, one pass, no boundary needed. You paid for tempdb and bought nothing.
Staging every column? Stage keys instead. Also Jeff, also correct. Carry the identifiers and join back to the base table for the wide columns. The width of what you copy is a separate decision from whether you copy at all, and it's the one people skip.
Small result feeding a small operation? Stop thinking about it. A few hundred rows, one pass, no reuse. Temp table, table variable, CTE, derived table -- pick whichever reads best. The difference is noise and you have better things to tune. Most of the temp tables I find in production live here, which is why they never show up in anybody's slow query report and never get removed.
What ties these all together is that none of them are about size on its own. A 250-million-row set read once should stay where it is. A 50,000-row set read four times should not. Volume tells us what a mistake will cost -- it doesn't tell us whether you're making one.
Which means Jeff and I mostly agree. He's arguing against the reflex, and I'm not defending the reflex either. I'm defending the tool. 'Temp tables are used too freely' and 'temp tables are a legitimate Optimizer control' are both true, and the second one is why the first one happens.
Foe when it's a reflex. Friend when it's a decision.
Demo Cleanup
USE master; GO ALTER DATABASE TempTableDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE TempTableDemo; GO
Thanks to Jeff for hosting, and for picking a topic with a true argument in it.
More to Read
Jeff Taylor: T-SQL Tuesday #201 Invitation -- Temp Tables, Friend or Foe?
T-SQL Tuesday -- the monthly blog party, started by Adam Machanic
Simple Talk: SQL Server Temp Tables -- Types, Syntax and Best Practices
sqlfingers: DOP Feedback, Part 2 -- Even Microsoft's Demo Didn't Get Me There
No comments:
Post a Comment