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

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.