top of page
Search

T-SQL Tuesday #201 Round-Up: Temp Tables, Friend or Foe?

T-SQL Tuesday

A couple of weeks ago, I asked a simple question with a loaded answer: are temp tables a friend or a foe? The responses did not disappoint. They ran from full-throated defense to a measured "it depends," and one of you built a lab. That's what I was looking for.


If there is a consensus, it is this: the reflex is the problem, not the tool. Almost everyone agreed that dumping data into a #temptable out of habit is a mistake. Almost everyone also had a case where a temp table was the right answer, and sometimes the only one. So let's get into it, in no particular order.


Rob Farley, LobsterPot Solutions

I can't believe Rob wrote a post; he was in the hospital and should have been resting! But I guess healing can be boring at times, so he decided to write a reply. (I hope you get better and are back to normal soon, Rob!) Rob is glad temp tables exist and uses them often. It seems he favors starting with a temp table and then converting it to not use a temp table, or debugging using a temp table for troubleshooting, which I have also done. Pulling remote results locally or bringing records across from another server, grabbing a tiny subset of a huge table. His rub is with people reaching for them, like cursors or scalar functions, as a procedural default. Go back and fix your code, or find a way to improve your query. Don't just find shortcuts to avoid work: he would rather a query be easy to read and perform well. As he puts it, pragmatism usually wins.


Rebecca Lewis, SQLFingers

Rebecca took the challenge literally and built a lab to find the case my argument does not cover. A temp table is not just a container; it is a plan boundary. On a dashboard query that needed the same aggregate four times, the no-temp version did eight times the logical reads, 58,556 against 7,284, even with a perfect covering index and freshly rebuilt statistics, and even though its cardinality estimates were actually better. The temp table won by computing the aggregate once and letting the small statements run serial instead of forcing one big parallel plan. Her rule of thumb is three questions: how many times will the result be read, how many times will it change, and how much are you moving to find out? Rebecca's blog highlights a great case for temp table usage; it is a must-read, and it makes me want to go figure out and see if I can write the same query without using a temp table. (I did it...see below...)


Brent Ozar, brentozar.com

Brent's rule is clean: default to CTEs, because they let SQL Server reorder the work whatever way is most efficient for your data and your version, and reach for a temp table only when it gets that wrong. Using a CTE to find the most popular Users.Location, and then the top 250 by reputation read 478,982 pages, more than the entire table, because the optimizer assumed an average location instead of the popular one and did 113,399 key lookups. Swap the CTE for a #temptable, and the reads drop by two-thirds: SQL Server auto-builds statistics on the single staged row, sees the value is "India," and compiles a fresh plan tuned for a big location. His caveat is honest: that fresh plan is effectively OPTION (RECOMPILE), so it is not free, and it is one case.


Chad Callihan, callihandata.com

Chad took the "whole family" prompt and walked indexed views, table variables, CTEs, and temp tables in turn. Indexed views he uses least, and he notes they are a poor fit for write-heavy tables. Table variables rarely, and he cheerfully copes by leaning on CTEs more than he should for one-off work. Temp tables are his pick when he needs to materialize data and lean on indexes and statistics, but he is clear they should not be the default sledgehammer. There is no clean one-to-four ranking: pick the right tool for the job.


Marlon Ribunal, SQL, Code, Coffee, Etc.

Marlon dug into the #temp versus @table variable question and put the disk-versus-memory myth to bed: both live in tempdb. The real difference is statistics. His skewed-data test showed the temp table estimating 9,000 and 10 rows correctly, while the table variable guessed 100 both times; his parallelism test showed an insert into a table variable running serially, whereas the temp table ran in parallel. His rule: a temp table when the optimizer needs to know the data; a table variable when the set is small and simple. His test scripts are on his GitHub.


Louis Davidson, Dr's Database Musings

Louis called this the easiest invitation ever, because his answer matched mine: exception, not reflex. He shared his query-writing order: a single simple statement first, then a subquery, then a CTE or derived table, and only then a temp table to take control of execution order. He picks apart the "filter early" myth and shows how forcing the optimizer's hand with a temp table often just hides the plan and strips the index you were relying on. Temp tables are his last-ditch tool. They can turn a two-hour query into two minutes, but those are the exceptions, not the rule.


Deb Melkin, Deb the DBA

Deb's stance is that temp tables are a useful tool that is very easy to misuse, and table variables even more so. She has war stories from both sides: a CTE that tanked in production until a temp table saved the day, and procs she later tuned by swapping temp tables for derived tables or consolidating several into one. She also covers the part people forget: tempdb metadata contention, memory-optimized tempdb, and why dropping temp tables inside a proc can actually hurt caching (<--THIS). Several good session links are tucked in there too. In my original post, I almost mentioned memory-optimized tempdb, but I'm not sure how widely used it is, and in most cases when I'm called in to tune a server, there are just regular temp tables everywhere!


Shane O'Neill, No Column Name

Shane went sideways in the best possible way, using the topic to explore temp table scope. He once "broke" a proc in Brent Ozar's First Responder Kit and finally works out why: temp tables reach into nested scopes, dynamic SQL, and called procs, whereas table variables and scalar variables do not. The worked examples of scope boundaries are worth your time. He lands firmly on temp tables: with billion-row tables and no window to run UPDATE STATISTICS, he will take every trick going.


Andy Brownsword, andybrownsword.co.uk

Andy leans toward the exception-not-the-rule camp, then makes the case for one exceptional exception: the temp table as an optimization boundary. When a query is a wall of text that is hard on you and on the optimizer, staging an intermediate result shrinks the scope, improves row estimates, and breaks the logic into readable chunks. His rule of thumb made me laugh: if you need a fresh brew before you can face a query, consider staging part of it into a temp table. - For sure! - Friend or foe? "Yes. Depending on how they are implemented."


My takeaway

In Rebecca's lab, which was a good example, she made a case for using tempdb as the query hit the same table/query multiple times, which increases the logical reads, of course.


I wanted to challenge myself and see if I could rewrite it and gain the same performance as using a temp table. I made a little modification, and I was able to return the same logical reads and less CPU, just like the temp table example.


Here it is if you want to see what I changed:

;WITH CustomerTotals
 AS
 (
     SELECT
         o.CustomerID,
         SUM(o.OrderTotal) AS TotalSpend,
         COUNT_BIG(*) AS OrderCount,
         AVG(SUM(o.OrderTotal)) OVER () AS AvgSpendAllCustomers,
         MAX(SUM(o.OrderTotal)) OVER () AS MaxSpendAllCustomers
     FROM
         dbo.tblOrders AS o
     WHERE
         o.OrderDate >= '2025-08-11'
     GROUP BY
         o.CustomerID
 )
SELECT
    TOP (100)
    c.CustomerName,
    ct.TotalSpend,
    ct.OrderCount,
    ct.AvgSpendAllCustomers,
    ct.MaxSpendAllCustomers
FROM
    CustomerTotals AS ct
    INNER JOIN dbo.tblCustomers AS c
        ON c.CustomerID = ct.CustomerID
WHERE
    ct.TotalSpend > ct.AvgSpendAllCustomers
ORDER BY
    ct.TotalSpend DESC;
GO

Here are the IO Stats

SQL Server parse and compile time: 
   CPU time = 5 ms, elapsed time = 5 ms.

(100 rows affected)
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 'tblCustomers'. Scan count 0, logical reads 215, 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 'tblOrders'. Scan count 1, logical reads 6455, 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 = 234 ms,  elapsed time = 239 ms.

Overall, we agree more than the title suggests. Nobody defended the reflex. Again, 'IT DEPENDS,' as we DBAs always say. If you are reusing the data in a result, a temp table might be the way to go, but it's not the norm or the initial 'go-to,' and as my example shows, a CTE could remove the need for a temp table.


I framed my invitation as "don't copy data the engine can already find." Several of you pushed the more precise version: a temp table is a plan boundary and an optimizer control, and sometimes that boundary is the entire point.


Thank you to everyone who wrote. This was fun, and I love all of the examples and experiences shared. If I missed your post, leave a comment, and I will add you.

 
 
 

Comments

©2021-2026 by Jeff Taylor

bottom of page