tempdb, Temp Tables and Table Variables: Which One When?

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0.5035.5, Cumulative Update 10); for the comparisons the database's compatibility level was switched between 140 and 170.

Guess First: How Many Rows Are in the Table Variable?

The first step of a report is to put customer 2's orders into an intermediate table; the second step is to count the lines of those orders. We do the same job with a table variable and with a temp table:

DECLARE @CustomerOrders TABLE (OrderId int PRIMARY KEY);
INSERT @CustomerOrders (OrderId) SELECT OrderId FROM dbo.Orders WHERE CustomerId = 2;

SELECT COUNT(*) AS lines, SUM(i.Quantity) AS books
FROM @CustomerOrders AS co
JOIN dbo.OrderItems AS i ON i.OrderId = co.OrderId;
GO
CREATE TABLE #CustomerOrders (OrderId int PRIMARY KEY);
INSERT #CustomerOrders (OrderId) SELECT OrderId FROM dbo.Orders WHERE CustomerId = 2;

SELECT COUNT(*) AS lines, SUM(i.Quantity) AS books
FROM #CustomerOrders AS co
JOIN dbo.OrderItems AS i ON i.OrderId = co.OrderId;

Both hold 17,359 rows. When planning the join, how many rows does the optimizer think the table variable has? Is the answer the same with the database at compatibility level 140 and at 170? The values we read from the actual plan:

compat  source            estimated rows  actual rows  join          OrderItems logical reads
140     @CustomerOrders   1               17359        Nested Loops  182749
140     #CustomerOrders   17359           17359        Hash Match    9964
170     @CustomerOrders   17359           17359        Hash Match    9964
170     #CustomerOrders   17359           17359        Hash Match    9964

At compatibility level 140 the estimate for the table variable is 1 row. For one row the optimizer chose the cheapest route, nested loops and one seek per order; 17,359 seeks, 182,749 logical reads. The same query finished in 9,964 reads with the temp table: an 18x difference. At compatibility level 170 the table variable got the right estimate too. This post explains the source of that difference, how temp tables, table variables and CTEs (Common Table Expressions) really behave, and tempdb, where they all live.

Table Variable Deferred Compilation

In the classic behavior, a statement that contains a table variable is compiled when the batch is compiled, that is, while the table variable is still empty. The optimizer has no row count and uses a fixed estimate: 1. SQL Server 2019, with compatibility level 150, brought table variable deferred compilation: compiling a statement that references a table variable is deferred until the statement runs for the first time, and the real row count at that moment is used. Temp tables already worked that way; that's why the two rows at 170 are identical.

Deferred compilation only solves the row count. There are no statistics on a table variable's columns. You see the difference when you filter on a column value:

DECLARE @Orders TABLE (OrderId int PRIMARY KEY, Status varchar(20) NOT NULL);
INSERT @Orders (OrderId, Status) SELECT OrderId, Status FROM dbo.Orders WHERE CustomerId = 2;
CREATE TABLE #Orders (OrderId int PRIMARY KEY, Status varchar(20) NOT NULL);
INSERT #Orders (OrderId, Status) SELECT OrderId, Status FROM dbo.Orders WHERE CustomerId = 2;

SELECT COUNT(*) AS cancelled FROM @Orders WHERE Status = 'Cancelled';
SELECT COUNT(*) AS cancelled FROM #Orders WHERE Status = 'Cancelled';

SELECT s.name AS stats_name, s.auto_created
FROM tempdb.sys.stats AS s
WHERE s.object_id = OBJECT_ID('tempdb..#Orders');

source    estimated rows  actual rows
@Orders   132             886
#Orders   886             886

stats_name|auto_created
----------|------------
PK__#Orders___C3905BCF79A520FD|0
_WA_Sys_00000002_B9AB0B8D|1

The estimate for the table variable is 132: very close to the square root of 17,359, which is the estimate the optimizer uses for equality without statistics. For the temp table an automatic statistics object (_WA_Sys_...) was created on the Status column and the estimate is exactly 886. The next join or the memory grant is built on that estimate. We saw how statistics are read in the 4th post of the series (Statistics and Cardinality Estimation).

Temp Tables: Statistics, Indexes and Recompiles

A local temp table (#) is a real table, only it lives in tempdb and belongs to the session. That gives you three things: column statistics, as many indexes as you want (also afterwards with CREATE INDEX) and parallel plans. The price comes from the same three. When statistics are updated, or when the content changes beyond a certain threshold, the statements that reference the temp table are recompiled. A temp table filled in a loop inside a procedure can trigger a recompile on every iteration. Most of the time that's a price worth paying for the right plan; rarely, in a short procedure that runs thousands of times a second, it turns into a CPU (Central Processing Unit) problem.

A global temp table (##) is visible to all sessions and is dropped after the session that created it closes and the last user lets go of it. Because it invites name clashes and unexpected sharing, it is rarely the right choice in application code.

Transaction Behavior and CTEs

The most often forgotten difference between the two is what happens on rollback:

DECLARE @Log TABLE (Step varchar(30));
CREATE TABLE #Log (Step varchar(30));
BEGIN TRANSACTION;
INSERT @Log VALUES ('reserve stock');
INSERT #Log VALUES ('reserve stock');
ROLLBACK;
SELECT (SELECT COUNT(*) FROM @Log) AS table_variable_rows, (SELECT COUNT(*) FROM #Log) AS temp_table_rows;

table_variable_rows|temp_table_rows
-------------------|---------------
1|0

A table variable doesn't take part in the user transaction; rollback doesn't undo its content. That's not a bug but a feature: in a CATCH block you can keep an error log that must survive the rollback in a table variable and then write it to a permanent table.

A CTE, on the other hand, isn't stored anywhere. It's a subquery with a name; every reference is expanded separately:

WITH CustomerTotals AS
(
    SELECT CustomerId, SUM(TotalAmount) AS Total
    FROM dbo.Orders
    WHERE CreatedAt >= '2026-10-01'
    GROUP BY CustomerId
)
SELECT (SELECT COUNT(*) FROM CustomerTotals) AS customers,
       (SELECT MAX(Total) FROM CustomerTotals) AS top_total;

customers|top_total
---------|---------
9059|228152.48
Table 'Orders'. Scan count 22, logical reads 15414, physical reads 0, ...

The clustered index of Orders is about 7,600 pages; 15,414 logical reads show it was scanned twice (scan count 22 because the plan is parallel). Because there's no index on CreatedAt, each reference does a full scan. If you use the same intermediate result more than once, writing it to a temp table means a single scan.

Which One When?

A rough rule: if it's above a few hundred rows and the next steps filter or join on column values, use a temp table. If it's small or just a list of keys, a table variable. Use a CTE for the readability of the query, not as a "materialization" tool.

Inside tempdb: Contention, Data Files and Metadata

Temp tables, table variables, sort and hash spills, cursors, the version store of RCSI (Read Committed Snapshot Isolation): they all live in tempdb. That's why tempdb itself can become a bottleneck. We can see how much space our own session uses:

CREATE TABLE #BigOrders (OrderId int PRIMARY KEY, CustomerId int, TotalAmount decimal(12,2));
INSERT #BigOrders SELECT OrderId, CustomerId, TotalAmount FROM dbo.Orders WHERE CustomerId = 1;
GO
SELECT user_objects_alloc_page_count, user_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage WHERE session_id = @@SPID;
SELECT SUM(used_page_count) AS used_pages
FROM tempdb.sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('tempdb..#BigOrders');

user_objects_alloc_page_count|user_objects_dealloc_page_count
-----------------------------|-------------------------------
800|144
used_pages
----------
655

The 200,000-row temp table uses 655 pages; the session counters only show completed tasks, which is why the query ran after a GO. For every new object tempdb touches its allocation pages: PFS (Page Free Space), GAM (Global Allocation Map) and SGAM (Shared Global Allocation Map). When many sessions create and drop small temp tables at the same time, latch waits build up on these pages; the wait type is PAGELATCH_UP or PAGELATCH_EX and the wait resource is a tempdb page such as 2:1:1. The classic fix is several data files of equal size. The lab server has 8 data files:

SELECT name, type_desc, size * 8 / 1024 AS size_mb FROM sys.master_files WHERE database_id = 2;
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS memory_optimized_tempdb_metadata;

name|type_desc|size_mb
----|---------|-------
tempdev|ROWS|8
templog|LOG|8
tempdev2|ROWS|8
tempdev3|ROWS|8
tempdev4|ROWS|8
tempdev5|ROWS|8
tempdev6|ROWS|8
tempdev7|ROWS|8
tempdev8|ROWS|8
memory_optimized_tempdb_metadata
--------------------------------
0

Setup recommends as many data files as logical processors up to 8, and 8 above that; if contention continues, add them four at a time. If the contention is in the system tables this time (tempdb's metadata), memory-optimized tempdb metadata, which came with SQL Server 2019, moves those tables to latch-free in-memory structures. It's a server-level setting and needs a restart; on the shared lab server it's off (0) and we didn't touch it.

Sending Bulk Data with TVPs

The most useful relative of the table variable is the TVP: sending a table from the application to a procedure in a single round trip. First you define a table type:

CREATE TYPE dbo.BookIdList AS TABLE (BookId int PRIMARY KEY);
GO
CREATE OR ALTER PROCEDURE dbo.GetStock
    @Books dbo.BookIdList READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SELECT s.BookId, s.QuantityOnHand, s.QuantityReserved
    FROM dbo.StockItems AS s
    JOIN @Books AS b ON b.BookId = s.BookId
    ORDER BY s.BookId;
END;
GO
DECLARE @Books dbo.BookIdList;
INSERT @Books (BookId) VALUES (1), (2), (3);
EXEC dbo.GetStock @Books = @Books;

BookId|QuantityOnHand|QuantityReserved
------|--------------|----------------
1|36|0
2|140|0
3|185|0

Querying the stock of the books in a cart means one query text and one plan, instead of a separate query text and a separate plan for every cart size with IN (@p0, @p1, ...). Inside, a TVP behaves like a table variable; its estimate also benefits from deferred compilation at compatibility level 150 and above. We saw how to send one from Dapper with AsTableValuedParameter in the SQL Server post of the Dapper series.

What's Different in SQL Server 2019/2022

Table variable deferred compilation and memory-optimized tempdb metadata came with SQL Server 2019; on 2019 and 2022 they require compatibility level 150 or higher. If you move an old database to 2022 and leave its compatibility level at 140, you'll keep seeing the 1-row estimate from the opening. SQL Server 2022 brought improvements that reduce latch contention on GAM and SGAM pages, so the same workload can show fewer PAGELATCH waits on 2022 than on 2019. The new thing in SQL Server 2025 is tempdb space resource governance: with Resource Governor you limit the tempdb space a workload group can use, and a query that tries to exceed the limit is cut off with error 1138:

ALTER WORKLOAD GROUP [default] WITH (GROUP_MAX_TEMPDB_DATA_MB = 20480);
ALTER RESOURCE GOVERNOR RECONFIGURE;

Because this is a server-level setting, we didn't run it in the shared lab. The documentation recommends not limiting the default workload group without setting up separate classification; otherwise ordinary queries can get 1138 while tempdb still has free space. The version store is outside this limit.

Watch Out in Production

Monitor tempdb. sys.dm_db_session_space_usage and sys.dm_db_task_space_usage show which session uses how much space; internal_objects_alloc_page_count shows spills, sorts and worktables. For the version store size look at sys.dm_tran_version_store_space_usage; a long open transaction can keep the version store from being cleaned up and fill tempdb.

Size tempdb in advance. tempdb stalls briefly during autogrowth; grow the data files to equal sizes up front according to the expected usage. With unequal files SQL Server writes more to the largest one and the effect of spreading contention is lost.

Check the compatibility level. Old reports written with table variables change plans when you move to compatibility level 150 and above; most of the time they improve, but a plan that happened to work well with the 1-row estimate can change too. We'll build the upgrade strategy with Query Store in post 19.

Common Pitfalls

The "table variables live in memory, temp tables on disk" myth. Both live in tempdb, and both stay in pages in memory if they're small. The difference isn't storage; it's statistics, transaction and recompile behavior.

Treating a CTE as a cache. A query that references the same CTE three times can do the underlying work three times. If you'll reuse the intermediate result, write it to a temp table.

Filling a temp table in a loop. Row-by-row INSERT inside a WHILE both writes many small log records to tempdb and triggers recompile thresholds. Fill it with a single INSERT ... SELECT.

Adding an index to a temp table inside a procedure afterwards. A separate CREATE INDEX after CREATE TABLE breaks temp table caching (reusing the tempdb object between procedure calls). Define indexes inline in CREATE TABLE.

A Small Experiment

★☆☆ Run the opening table variable query at compatibility level 170 with OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV')). What are the estimate and the OrderItems logical reads?

★★☆ Add an inline index on Status to the @Orders table variable (INDEX IX_Status (Status)). Does the Status = 'Cancelled' estimate change? Why?

★★★ With Dapper, run the cart stock query first with WHERE BookId IN @Ids, then with the dbo.BookIdList TVP, for carts of 1, 5 and 20 books. Compare how many different query texts and plans appear in sys.dm_exec_query_stats.

In this post we mentioned waits in tempdb, wait types and DMVs (Dynamic Management Views) here and there. In the next post we turn them into a diagnostic method: solving the "the database is slow" complaint step by step with wait statistics, DMVs and Extended Events.