Temporary Tables vs Table Variables, Statistics, Recompiles, and Scope

Learning outcomes

A ServiceHub procedure stages 30,000 candidate work orders before joining them back to the fact table. One developer replaces a local temporary table with a table variable because “table variables live in memory.” The new version sometimes improves compilation overhead and sometimes chooses a catastrophically weak join shape. The slogan is wrong: both constructs can consume tempdb, and their optimizer metadata differs.

Lab bootstrap

USE master;GOIF DB_ID(N'ServiceHubWorkspaceLab') IS NULL  CREATE DATABASE ServiceHubWorkspaceLab;GOALTER DATABASE ServiceHubWorkspaceLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubWorkspaceLab;GOIF SCHEMA_ID(N'lab12') IS NULL EXEC(N'CREATE SCHEMA lab12 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab12.WorkOrderFact',N'U') IS NULLBEGIN  CREATE TABLE lab12.WorkOrderFact  (    work_order_id bigint IDENTITY(1,1) NOT NULL,    customer_id int NOT NULL,    region_code char(3) NOT NULL,    status varchar(12) NOT NULL,    priority tinyint NOT NULL,    opened_at datetime2(0) NOT NULL,    amount decimal(12,2) NOT NULL,    payload varchar(400) NULL,    CONSTRAINT PK_lab12_WorkOrderFact PRIMARY KEY CLUSTERED(work_order_id)  );  ;WITH n AS  (    SELECT TOP (60000)      ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id) AS n    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b  )  INSERT lab12.WorkOrderFact(customer_id,region_code,status,priority,opened_at,amount,payload)  SELECT CASE WHEN n <= 30000 THEN 1 ELSE 2 + n % 4999 END,         CASE WHEN n % 3=0 THEN 'N01' WHEN n % 3=1 THEN 'W02' ELSE 'E03' END,         CASE WHEN n % 19=0 THEN 'ESCALATED' WHEN n % 5=0 THEN 'CLOSED' ELSE 'OPEN' END,         CONVERT(tinyint,1 + n % 5),         DATEADD(minute,n,'2026-01-01T00:00:00'),         CAST(10 + (n % 20000)/10.0 AS decimal(12,2)),         CASE WHEN n % 13=0 THEN REPLICATE('x',350) ELSE REPLICATE('x',40) END  FROM n;  CREATE INDEX IX_lab12_RegionStatus    ON lab12.WorkOrderFact(region_code,status,opened_at)    INCLUDE(customer_id,priority,amount);END;GO

The lab database uses compatibility 170, so table-variable deferred compilation is eligible unless explicitly disabled. The examples deliberately use enough rows to make estimate quality visible, but the exact physical plan can differ with memory, cores, statistics and CU.

1. Scope and storage: neither object is simply “in memory”

The important design question is not “which syntax is faster?” It is: what information does the optimizer need, how many rows will actually appear, will later statements reuse the intermediate result, do we need indexes, and how volatile is the distribution?

2. Observe the estimate difference instead of memorizing an old one-row rule

Before SQL Server 2019 compatibility-level improvements, table variables were famous for poor fixed cardinality assumptions. Table-variable deferred compilation changes the initial compilation point: the statement referencing the table variable can compile after it has been populated and use the actual first-execution row count. This significantly improves many plans, but it does not create a histogram or solve skew between later executions.

USE ServiceHubWorkspaceLab;GOSET STATISTICS IO ON;SET STATISTICS TIME ON;GODECLARE @Candidate TABLE(  work_order_id bigint NOT NULL PRIMARY KEY,  customer_id int NOT NULL,  amount decimal(12,2) NOT NULL);INSERT @Candidate(work_order_id,customer_id,amount)SELECT work_order_id,customer_id,amountFROM lab12.WorkOrderFactWHERE status='OPEN' AND priority<=3;SELECT c.customer_id,COUNT_BIG(*) AS order_count,SUM(c.amount) AS amountFROM @Candidate AS cJOIN lab12.WorkOrderFact AS f ON f.work_order_id=c.work_order_idGROUP BY c.customer_id;GODROP TABLE IF EXISTS #Candidate;SELECT work_order_id,customer_id,amountINTO #CandidateFROM lab12.WorkOrderFactWHERE status='OPEN' AND priority<=3;CREATE UNIQUE CLUSTERED INDEX CX_Candidate ON #Candidate(work_order_id);CREATE INDEX IX_Candidate_Customer ON #Candidate(customer_id) INCLUDE(amount);SELECT c.customer_id,COUNT_BIG(*) AS order_count,SUM(c.amount) AS amountFROM #Candidate AS cJOIN lab12.WorkOrderFact AS f ON f.work_order_id=c.work_order_idGROUP BY c.customer_id;GOSET STATISTICS IO OFF;SET STATISTICS TIME OFF;DROP TABLE #Candidate;GO

Capture actual execution plans. Compare estimated and actual rows at the table-variable/temp-table access operator and at the join. On compatibility 170, the table variable may have a much more realistic initial row estimate than old tutorials predict. The temp table can still have a richer statistics story and can support additional indexes when later predicates need them.

3. Prove what statistics exist

DROP TABLE IF EXISTS #Stage;SELECT work_order_id,customer_id,status,priority,amountINTO #StageFROM lab12.WorkOrderFactWHERE region_code='N01';GO-- Force predicates that can cause useful temp-table statistics to appear.SELECT COUNT_BIG(*) FROM #Stage WHERE customer_id=1;SELECT COUNT_BIG(*) FROM #Stage WHERE priority=5;GOSELECT s.name,s.auto_created,s.user_created,s.has_filter,       sp.last_updated,sp.rows,sp.rows_sampled,sp.modification_counterFROM tempdb.sys.stats AS sOUTER APPLY tempdb.sys.dm_db_stats_properties(s.object_id,s.stats_id) AS spWHERE s.object_id=OBJECT_ID('tempdb..#Stage')ORDER BY s.stats_id;GODROP TABLE #Stage;

A temp table’s statistics can improve selectivity estimates after staging, and modifications can cross thresholds that trigger statistics updates/recompiles. That extra compilation work is not automatically bad: a recompile can be the mechanism that prevents a stale intermediate-result assumption from poisoning the rest of the plan.

4. The deliberately wrong approach: “table variables never cause recompiles, so always use them”

A team that values only compile count can choose a table variable for a large, skewed intermediate result and create a worse overall system: fewer compilations but more reads, poor joins, oversized/undersized grants, spills, or long CPU duration. Conversely, creating and indexing a temp table for three rows can be needless overhead. The object is a plan-design choice.

Another wrong approach is disabling table-variable deferred compilation because an old diagnostic script expects the historical estimate. If you need to compare behaviors, use a scoped experiment and restore the prior setting; do not alter production compatibility or database-scoped features casually.

USE ServiceHubWorkspaceLab;GOSELECT compatibility_level FROM sys.databases WHERE database_id=DB_ID();SELECT name,value,value_for_secondaryFROM sys.database_scoped_configurationsWHERE name='DEFERRED_COMPILATION_TV';GO

5. Memory-optimized alternatives are architecture, not a syntax trick

SQL Server supports memory-optimized table types/tables under In-Memory OLTP, and in some workloads they can remove tempdb pressure. But they require memory-optimized infrastructure, sizing, schema support and operational understanding. SQL Server 2025 supports In-Memory OLTP across editions with edition-specific limits, but a learner should not replace every #temp table with an XTP object simply to avoid tempdb. Prove that tempdb is the bottleneck first.

For most application procedures, start with the simplest structure that gives correct semantics and a stable plan. If row volume or filter distribution matters to downstream joins, a temp table with statistics and a focused index can be the clearer contract. If the intermediate result is small and uncomplicated, a table variable can be entirely reasonable—especially with modern deferred compilation.

Lab cleanup and production judgment

Leave ServiceHubWorkspaceLab in place for the rest of Chapter 12. Local #temp objects disappear when dropped or the session ends; table variables disappear at scope exit. Do not infer “no cleanup needed” in production—high-frequency create/drop activity can still create allocation/metadata pressure even though the objects are transient.

Authoritative references

• tempdb database — temporary objects and caching behavior
• Intelligent Query Processing details — table-variable deferred compilation
• Tables — temporary-table behavior and recompilation improvements
• sys.dm_db_session_space_usage — session tempdb allocation evidence
• SQL Server 2025 editions and supported features — In-Memory/edition boundaries