Friday, 20 May 2011

T-SQL: Table Variables Ignore Rollbacks

I've been spending more and more time on SQL Server Central over the past month or so and am finding their Question of the Day series fun (and educational!). The question from Oct 6th, 2010 particularly caught my eye and I thought i'd jot down an example here to illustrate its point:

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

/* add this crazy stuff in so i can use syntax highlighter