Table Variables Archives - https://sqlmaster.mssqlplanner.com

Tag: Table Variables

Temp Table vs Table Variable vs Global Temp Table: A Decision Framework for SQL Server

Three temp object types, three genuinely different jobs. Now that you’ve built all three, here’s how to choose without guessing — and why this exact question shows up so often in interviews.

The Decision Tree

Situational Cheat Sheet

Situation	Right tool
Staging a large intermediate result in a complex report	Local temp table (#)
Small lookup list of a few rows inside a procedure	Table variable (@)
Error/audit log that must survive a transaction rollback	Table variable (@)
Sharing a snapshot between two active debugging sessions	Global temp table (##)
Data needs real statistics for the optimizer to make good join choices	Local temp table (#)
You need to add an index only after seeing the shape of the data	Local temp table (#) — table variables can’t be altered post-declaration

This Is a Genuinely Common Interview Question

“What’s the difference between a temp table and a table variable?” comes up constantly, precisely because the shallow answer (“table variables are smaller/faster”) is folklore, not fact — in practice, on non-trivial row counts, a table variable’s lack of real statistics can make it slower, not faster, exactly because the optimizer’s row estimate is wrong. The strongest interview answer isn’t a definition — it’s the rollback behavior from the previous lesson, because it’s the one difference that actually changes what your code does, not just how fast it runs.

-- A one-line answer worth having ready: "table variables don't roll back with the
-- surrounding transaction, and historically carry no real statistics for the optimizer"
SELECT 'Table variable' AS type, 'Survives rollback, weak statistics' AS behavior
UNION ALL
SELECT 'Temp table', 'Rolls back with transaction, real statistics';

Practice tip: Before moving to stored procedures in the next chapter, pick one real multi-step problem — even something simple like “top 3 highest earners per department” — and consciously decide, using this decision tree, which temp object type (if any) you’d use to solve it. Comparing your reasoning against a CTE-only solution is a useful check too: sometimes the right answer is none of the three.

Enjoyed this?

Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.

📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook

Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server for Developers & DBAs, coming soon on this site.

July 22, 2026

Table Variables in SQL Server: The Rollback Behavior That Actually Matters

Table variables look like a smaller, simpler version of a temp table. The real difference is behavioral, not just syntactic — and it’s specific enough to be one of the most commonly asked SQL Server interview questions, covered again in Chapter 11.

Using One

DECLARE @DriverTotals TABLE (
    driver_name NVARCHAR(100),
    total_fare  DECIMAL(10,2)
);

INSERT INTO @DriverTotals
SELECT driver_name, SUM(fare_usd)
FROM dbo.TripAdvanced
GROUP BY driver_name;

SELECT * FROM @DriverTotals ORDER BY total_fare DESC;

The Behavior That Actually Surprises People in Production

DECLARE @Log TABLE (msg NVARCHAR(200));
CREATE TABLE #Log (msg NVARCHAR(200));

BEGIN TRAN;
    INSERT INTO @Log VALUES ('table variable row');
    INSERT INTO #Log VALUES ('temp table row');
ROLLBACK;

SELECT * FROM @Log;  -- still has the row!
SELECT * FROM #Log;  -- empty — rolled back
DROP TABLE #Log;

If you’re logging errors into a table variable inside a transaction that then fails and rolls back, the table variable’s contents survive — that’s often exactly the behavior you want for an error log, and exactly why table variables exist as a distinct tool, not just “a smaller #temp table.” The underlying reason: a table variable is not itself part of the surrounding transaction’s log the same way a #temp table’s DML operations are — SQL Server treats it more like an ordinary local variable for durability purposes, even though it physically also lives in tempdb.

-- The realistic use case: an error log that survives the very rollback it's reporting on
BEGIN TRY
    BEGIN TRAN;
        UPDATE dbo.Employee SET salary = salary * 1.1;
        INSERT INTO @Log VALUES ('About to check a business rule...');
        IF EXISTS (SELECT 1 FROM dbo.Employee WHERE salary > 1000000)
            THROW 51000, 'Salary cap exceeded', 1;
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    INSERT INTO @Log VALUES ('Rolled back: ' + ERROR_MESSAGE());
END CATCH;
SELECT * FROM @Log; -- both log entries are here, even though the UPDATE itself was undone

Quick Comparison

	#temp table	@table variable
Scope	Session (and nested calls)	Batch or procedure only
Statistics	Real, maintained	Historically none (improved somewhat in 2019+ with deferred compilation, but still generally weaker)
Transaction rollback	Rolled back	Survives
Can be altered after creation	Yes (ADD COLUMN, CREATE INDEX)	No — structure is fixed at DECLARE time

That last row matters in practice too: if you need to add an index to your intermediate result after seeing what the data looks like, or need to ALTER its structure partway through a script, a table variable can’t do that — its full structure must be declared upfront.

Practice tip: Rebuild the TRY/CATCH error-logging example above yourself, and swap @Log for a #Log temp table to see the log entries vanish along with the rollback. That side-by-side comparison, run yourself, is the fastest way to make this genuinely memorable rather than a fact you memorized for an interview.

Enjoyed this?

Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.

📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook

Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server for Developers & DBAs, coming soon on this site.

July 18, 2026