Temp Tables vs Table Variables

Status: Reviewed (deep-dive v1)
Stack note: Warehouse procs (SPIKE boards, evaluate/ack) and PowerShell-fed loads often stage rows mid-proc — pick the right temp structure or you inherit bad estimates.
Pairs with: SQL-11 (mTVF @table pitfalls), SQL-12 (procs — next on shortlist), SQL-07 (mystery plans).
Goal: Know when #temp / ##temp / @table / table types win, and the cardinality traps.

Quick chooser

Situation	Prefer	Why
Medium/large rowsets, need stats + indexes	`#temp`	Statistics; can add indexes; better plans
Tiny lookup (few rows), simple pass-through	`@table`	Light; no tempdb name churn; OK when estimates don’t matter
Reuse shape across procs / TVP from Blazor or PS	User-defined table type + TVP	Typed contract; still table-var semantics inside
Share across sessions (rare)	`##global` temp	Almost never — prefer real table or Service Broker
Multi-statement TVF return	`@table` inside TVF	Required by TVF syntax — consider proc + `#temp` instead for heavy work

The options

1. Local temp table (`#t`)

CREATE TABLE #Orders (
  OrderId int NOT NULL PRIMARY KEY,
  CustomerId int NOT NULL,
  Total money NOT NULL
);
CREATE INDEX IX_Orders_Customer ON #Orders(CustomerId);

INSERT #Orders (…)
SELECT … FROM #Orders WHERE CustomerId = @Id;
-- dropped at end of session/scope (proc: end of proc unless ##)

Pros: Optimizer can use statistics; indexes (incl. after load); ALTER/TRUNCATE; behaves like a real table for plans.
Cons: Tempdb allocation/logging; name uniqueness per session; recompiles when stats change.

Use: Staging in usp_* boards, joins of hundreds+ rows, “load then index then query.”

2. Table variable (`@t`)

DECLARE @Orders TABLE (
  OrderId int PRIMARY KEY,
  CustomerId int NOT NULL,
  Total money NOT NULL
  -- indexes mostly via constraints at declare time (version-dependent extras exist)
);
INSERT @Orders …;
SELECT … FROM @Orders;  -- often fixed/low cardinality guess historically

Pros: Scoped cleanly; less locking metadata noise; fine for small sets.
Cons: Weak/missing stats historically → bad estimates, under/over memory grants; harder to index after the fact; no TRUNCATE like #temp.

Use: A handful of IDs, parameter unpack, tiny maps. Not your 50k-row heat-map staging.

3. Table-valued parameter (TVP)

-- type once
CREATE TYPE dbo.OrderIdList AS TABLE (OrderId int PRIMARY KEY);
-- proc
CREATE PROC dbo.usp_Orders_ByIds @Ids dbo.OrderIdList READONLY AS
  SELECT o.* FROM Sales.Orders o JOIN @Ids i ON i.OrderId = o.OrderId;

Pros: Clean Blazor/PS → SQL contract (no CSV string splitting).
Cons: Readonly inside proc; still table-variable-like estimates — for big TVPs, dump to #temp then join.

4. Global temp (`##t`) — usually skip

Visible to other sessions until last user disconnects. Prefer permanent staging table with a run id for Agent jobs.

Performance checklist

[ ] Row count unknown or > ~100–1000? Default #temp
[ ] Need nonclustered index after load? #temp
[ ] Joining staged data to big permanent tables? Prefer #temp so estimates aren’t 1-row guesses
[ ] TVP large? INSERT #t SELECT * FROM @TVP then query #t
[ ] mTVF slow? Same root cause as @table — rewrite as proc + #temp when possible
[ ] Measure: actual vs estimated rows on the temp operator; tempdb free space / contention under load
[ ] Don’t SELECT * INTO #t in a loop — one create, batched inserts

Patterns for this estate

Workload	Pattern
SPIKE evaluate gathering findings	`#Findings` then merge to `Alert`
Backup heat / capacity snapshots	`#Stage` from collectors → merge procs
Blazor multi-select filters	TVP of ids → optional dump to `#temp` if list can be large
PowerShell bulk load	Bulk insert to staging table (permanent or `#`) — not row-by-row `@table`
UDF returning many rows	Prefer inline TVF or proc; avoid fat mTVF `@table`

Tempdb hygiene (ops)

[ ] Size tempdb data files evenly; watch growth during Agent storms
[ ] Version store / long transactions can pin tempdb — separate from “I used @table to save tempdb” myths (both use tempdb)
[ ] Autogrowth storms → pre-size; alert on free space (SQL-06 / SPIKE-04)

Common failure patterns

Symptom	Likely cause
Nested loop + huge outer, tiny estimate on “temp”	`@table` / TVP / mTVF estimate
Spills / wrong memory grant	Same
Tempdb full during nightly jobs	Fat `#temp` + parallelism + many sessions; check growth
“It was fast in SSMS with 10 rows”	Plan for small `@table` doesn’t scale

Tiny lab

Load 50k rows into @t and #t; join each to a big table — compare estimates and duration
Add index on #t after load; retry
Team default: #temp unless proven tiny

Tie-ins

SQL-11: mTVF forces @table semantics — reason we push iTVF / procs
SQL-07 / SPIKE-05: Board procs that stage then aggregate should use #temp
SQL-15 (shortlist): TRY/CATCH around staging + cleanup

Done when

[ ] Team default written: #temp for staging; @table/TVP for small or typed input
[ ] Hot procs audited for large @table joins
[ ] This note linked from Knowledge Base list

SQL Dude — SQL-13 Temp Tables vs Table Variables v1