Look at the differences between the 2 sql batches, one using a temp table and one using a table variable:
Script 1: Temp Table
CREATE TABLE #T (i INT)
INSERT INTO #T SELECT 1
INSERT INTO #T SELECT 2
INSERT INTO #T SELECT 3
BEGIN TRAN
INSERT INTO #T SELECT 4
INSERT INTO #T SELECT 5
INSERT INTO #T SELECT 6
ROLLBACK TRAN
-- only 3 rows
SELECT * FROM #T
Script 2: Table Variable
DECLARE @t TABLE (i INT)
INSERT INTO @t SELECT 1
INSERT INTO @t SELECT 2
INSERT INTO @t SELECT 3
BEGIN TRAN
INSERT INTO @t SELECT 4
INSERT INTO @t SELECT 5
INSERT INTO @t SELECT 6
ROLLBACK TRAN
-- 6 rows!!
SELECT * FROM @t
Although the SQLServerCentral article suggests there isn't any documentation, there is a comment in Books Online which does explain the behaviour albeit that its not that easy to find.
http://msdn.microsoft.com/en-us/library/ms175010.aspx
"because table variables have limited scope and are not part of the persistent database, they are not affected by transaction rollbacks."
No comments:
Post a Comment