tempdb and

In one line: a #temp table buys you exactly two things a CTE or a table variable can’t: a statistics histogram for estimates, and one evaluation you can reference many times. If you need neither, don’t materialize.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/tempdb on GitHub.

What you’ll see

  • A CTE referenced twice doing the work twice. In the demo: 8,416 reads once, 16,832 twice, and 8,867 from a #temp table referenced twice.
  • A table variable estimating 100 rows for a predicate that returns 9,000, and also 100 for one that returns 10, even with an index on it.
  • A load into a table variable running serially while the same load into a #temp table goes parallel.
  • Msg 468, a collation conflict, when a #temp table joins a database whose collation differs from tempdb’s.
  • A procedure that slowly gets more expensive because its temp table is rebuilt on every call instead of being cached.

Why it happens

  • A CTE is a macro, expanded wherever it’s referenced.
  • Table variables have no histogram. An index gives the optimizer something to seek on, not a distribution to reason about.
  • Modifying a table variable can’t use a parallel plan. Reading from one can.
  • CREATE TABLE #t takes tempdb’s collation for character columns. SELECT INTO takes the source column’s.

What to do

  • Use a #temp table when you need a histogram (skewed data, joins that depend on the row count) or the same set more than once.
  • Keep temp table caching:
    • declare indexes inline in the CREATE TABLE
    • leave constraints unnamed
    • stay under about 8 MB; in the demo, caching stopped right at that boundary
  • Add COLLATE DATABASE_DEFAULT to every character column in a hand-created #temp.
  • Use a table variable to keep diagnostics inside a transaction you’ll roll back. It’s the only kind that survives ROLLBACK: 1 row versus 0.

What doesn’t help

  • Avoiding an explicit DROP TABLE #t, or avoiding SELECT INTO, to protect caching. In the demo both cached fine. These broke caching:
    • a named constraint
    • a CREATE INDEX after the CREATE TABLE
    • an ALTER TABLE
    • dynamic SQL
    • a table over 8 MB
  • Trace flags 1117 and 1118. Their behaviour has been built into tempdb since SQL Server 2016. In a startup parameter list they’re cargo cult.
  • Using any temp table inside a function. It’s not allowed at all.

Check your server

-- How often temp tables are being created (an instance-wide counter: sample it twice and subtract),
-- and which databases have a different collation from tempdb.
SELECT counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Temp Tables Creation Rate', 'Active Temp Tables');

SELECT name, collation_name,
       DiffersFromTempdb = CASE WHEN collation_name <> CAST(DATABASEPROPERTYEX('tempdb', 'Collation') AS sysname) THEN 1 ELSE 0 END
FROM sys.databases
WHERE database_id > 4;

See it happen

The tempdb demo measures six scenarios:

  1. CTE reuse
  2. estimates under skew
  3. parallelism
  4. what breaks temp table caching
  5. the 8 MB cliff
  6. collation and the two hard stops

Related: MSTVFs and table variables · RCSI and snapshot isolation · parallelism

Measured on SQL Server 2022 CU25 (16.0.4255.1), 8 schedulers. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights