SQL Server: Temp Tables vs Table Variables

Please follow below differences between Temp Table and Table Variable.

1. In transactions, Temp Variables survive, on the other hand, Temp Tables do not.

DROP TABLE #TempTable;

-- Declare Table Variable and Temp Table
DECLARE @TempTableVar TABLE (id [INT])
CREATE TABLE #TempTable (id [INT])

-- Insert Sample Data
INSERT INTO @TempTableVar(id) VALUES(1);
INSERT INTO #TempTable(id) VALUES(1);

-- Make a Select
SELECT * FROM @TempTableVar
SELECT * FROM #TempTable

-- Start a Transaction
BEGIN TRAN

    -- Insert Sample Data
    INSERT INTO @TempTableVar(id) VALUES(2);
    INSERT INTO #TempTable(id) VALUES(2);

-- Rollback the present transaction
ROLLBACK

-- Table Variable Record 2 survives
SELECT * FROM @TempTableVar 
-- Temp Table Record 2 never survives
SELECT * FROM #TempTable 

And the output goes like this –

temptables


2.  Table variables have a scope associated with them. If a table variable is declared in a stored procedure, it is local to that stored procedure and cannot be referenced in a nested procedure. But not in case of Temp Tables, they can be used in nested stored procedures too.

3. The modification counter threshold for a Temp table is 6. This implies that when a stored procedure that creates a Temp table inserts 6 or more rows into this table, Stored Procedures will be recompiled as soon as this table is accessed. We can make this count to 500 (for permanent tables) by using KEEP PLAN. This modification counter doesn’t have any effect on table variable.

4. For long data/rows, use Temp Table. For short data use Temp Variable.

5.  Both Temp tables and table variables are kept in memory until their size reaches a certain threshold after which they are pushed to disk.

6. Table variables cannot be altered after they have been declared.

7. We cannot explicitly add an index to a table variable, however we can create a system index through a primary key, and we can add as many indexes through unique constraints.

8. We an drop a Temp table, but we cannot drop a table variable explicitly. It will be gone once it is out of scope.

You may also like...

  • James

    Great post!!!