Originally published at SQL & Cloud Lab.

This post was drafted with AI assistance.

SQL Server gives you two ways to hold intermediate results: temporary tables (#temp) and table variables (@table). They look almost the same, and many developers pick one out of habit. But they behave differently in ways that can turn a 2-second procedure into a 2-minute one. Here is how to choose.

SQL Server temp tables vs table variables comparison

The syntax

-- Temporary table CREATE TABLE #Employees ( EmployeeId INT PRIMARY KEY, DepartmentId INT NOT NULL ); -- Table variable DECLARE @Employees TABLE ( EmployeeId INT PRIMARY KEY, DepartmentId INT NOT NULL ); 

A common myth is that table variables live in memory and temp tables live on disk. In fact, both are created in tempdb, and both stay in memory while there is enough of it. The real differences are elsewhere.

Difference 1: statistics and row estimates

This is the one that matters most for performance.

  • Temp tables get column statistics, just like normal tables, so the optimizer can estimate how many rows a filter or join will return.
  • Table variables do not get column statistics. In older versions, the optimizer also assumed a table variable held just one row when the plan was compiled.

With 100,000 rows in a table variable, that one-row guess leads to plans built for tiny inputs, such as nested loops joins that run 100,000 times. Since SQL Server 2019 (database compatibility level 150 and above), table variable deferred compilation uses the actual row count at first compile, which helps a lot. There are still no column statistics, though, so estimates for filters and joins on table variable columns remain weak.

Difference 2: indexes

  • Temp tables can have any index, added at any time with CREATE INDEX.
  • Table variables can only have indexes declared inline when they are created, such as a primary key, unique constraint or inline INDEX definition.
DECLARE @Orders TABLE ( OrderId INT PRIMARY KEY, CustomerId INT NOT NULL, INDEX IX_Customer NONCLUSTERED (CustomerId) ); 

Difference 3: transactions and rollback

Changes to a table variable are not undone by a ROLLBACK. That sounds like a bug, but it is useful: you can collect error or audit details in a table variable inside a transaction and still read them after rolling back.

DECLARE @Log TABLE (Msg NVARCHAR(200)); BEGIN TRAN; INSERT INTO @Log VALUES (N'Step 1 failed'); ROLLBACK; SELECT * FROM @Log; -- the row is still there 

Difference 4: scope and recompiles

  • A table variable exists only in the batch or procedure that declares it.
  • A #temp table is visible to the procedure that creates it and to procedures it calls, and is dropped when the creating scope ends.
  • Temp tables can trigger statement recompiles as their data changes, because statistics change. That is usually what gives them good plans, but in very high-frequency procedures the recompiles can add CPU overhead.

Side-by-side comparison

#temp table @table variable
Stored in tempdb tempdb
Column statistics Yes No
Row count at compile Known Known from SQL Server 2019 (compat 150+); assumed 1 before
Indexes Any, any time Inline at declaration only
Affected by ROLLBACK Yes No
Visible to called procedures Yes No
Recompiles Possible Rare

Which should you use?

  • Use a #temp table when the data is more than a few hundred rows, when you join or filter on it, or when you need extra indexes. This covers most real reporting and batch work.
  • Use a table variable for small sets (lookups, IDs, a few dozen rows), in very frequently called code where recompiles matter, or when you need data to survive a rollback.
  • When in doubt, test both. Compare the actual execution plans and logical reads with realistic data volumes, not with the ten rows in your development database.

Related reading:

More SQL Server guides on my blog, SQL & Cloud Lab.