2017-07-17

Table Variable vs Temporary Table

There's a subtle difference between table variable and temporary table, but it's quite critical for sql developer. In a transaction, i.e. statements between BEGIN TRAN and COMMIT/ROLLBACK, while temporary tables will be affected by rollback, table variables are NOT part of transaction. E.g.
CREATE TABLE #t (i int);
DECLARE @t table (i int);
BEGIN TRAN
INSERT @t VALUES (1);
INSERT #t VALUES (1);
SELECT 'Before Rollback #Tmp', * FROM #t;
SELECT 'Before Rollback @var', * FROM @t;
ROLLBACK
SELECT 'After Rollback #Tmp', * FROM #t;
SELECT 'After Rollback @var', * FROM @t;
DROP TABLE #t;

Result:
 Below are their differences provided by Microsoft:

No comments:

Post a Comment