As I’ve mentioned in my previous post, there is a catch when using a table variable as a part of a transaction. It behaves differently than a regular or a temporary table. To see this behavior, just run the attached script, once with @Rollback = 0 and once with @Rollback = 1 .
Transact-SQL
SET NOCOUNT ON; -- RollBack or not ? DECLARE @RollBack BIT = 0; -- 1 DECLARE @Table TABLE ( Id INT , Value VARCHAR(15) ); INSERT INTO @Table VALUES (1,'Update Me') , (2,'Delete Me'); SELECT * INTO #Table FROM @Table; SELECT TmpTable = T.Value , VarTable = V.Value FROM #Table AS T FULL OUTER JOIN @Table AS V ON T.Id = V.Id; SELECT Action = 'Begin Tran'; BEGIN TRAN -- Temp table DML INSERT INTO #Table VALUES (3,'I was inserted'); UPDATE #Table SET Value = 'I was updated' WHERE Value = 'Update Me'; DELETE #Table WHERE Value = 'Delete Me'; -- Table variable DML INSERT INTO @Table VALUES (3,'I was inserted'); UPDATE @Table SET Value = 'I was updated' WHERE Value = 'Update Me'; DELETE @Table WHERE Value = 'Delete Me'; SELECT TmpTable = T.Value , VarTable = V.Value FROM #Table AS T FULL OUTER JOIN @Table AS V ON T.Id = V.Id; -- RollBack ?? IF @RollBack = 1 BEGIN SELECT Action = 'Rollback Tran'; ROLLBACK TRAN; END; ELSE BEGIN SELECT Action = 'Commit Tran'; COMMIT TRAN; END; SELECT TmpTable = T.Value , VarTable = V.Value FROM #Table AS T FULL OUTER JOIN @Table AS V ON T.Id = V.Id; DROP TABLE #Table; GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
SET NOCOUNT ON;
-- RollBack or not ?
DECLARE @RollBack BIT = 0; -- 1
DECLARE @Table TABLE
(
Id INT ,
Value VARCHAR(15)
);
INSERT INTO
@Table
VALUES
(1,'Update Me') ,
(2,'Delete Me');
SELECT *
INTO #Table
FROM @Table;
SELECT
TmpTable = T.Value ,
VarTable = V.Value
FROM
#Table AS T
FULL OUTER JOIN
@Table AS V
ON T.Id = V.Id;
SELECT Action = 'Begin Tran';
BEGIN TRAN
-- Temp table DML
INSERT INTO
VALUES (3,'I was inserted');
UPDATE #Table
SET Value = 'I was updated'
WHERE Value = 'Update Me';
DELETE #Table
WHERE Value = 'Delete Me';
-- Table variable DML
INSERT INTO
@Table
VALUES (3,'I was inserted');
UPDATE @Table
SET Value = 'I was updated'
WHERE Value = 'Update Me';
DELETE @Table
WHERE Value = 'Delete Me';
SELECT
TmpTable = T.Value ,
VarTable = V.Value
FROM
#Table AS T
FULL OUTER JOIN
@Table AS V
ON T.Id = V.Id;
-- RollBack ??
IF @RollBack = 1
BEGIN
SELECT Action = 'Rollback Tran';
ROLLBACK TRAN;
END;
ELSE
BEGIN
SELECT Action = 'Commit Tran';
COMMIT TRAN;
END;
SELECT
TmpTable = T.Value ,
VarTable = V.Value
FROM
#Table AS T
FULL OUTER JOIN
@Table AS V
ON T.Id = V.Id;
DROP TABLE #Table;
GO
The script is pretty simple:
Inserts identical data to a temp table and to a table variable
Opens a transaction
Perform identical DML commands (insert, update, delete) on both tables
Commit / Rollback the transaction
You should get the following results:
When we commit the transaction, both tables behave as expected. But when we rollback the transaction, the temp table rolls back while the table variable doesn’t.
Why is that??
The answer lies in the transaction log. Well actually…it doesn’t, and that’s the whole point.
Every time an object in the DB changes schema or data is being modified, the change is logged in the transactions log. But a table variable is not an object, it’s a variable, and the record set it holds is not data, it’s a value. Being as such, any change to a table variable is never logged in the transaction log. In other words, SQL Server never keeps any record on table variable history, and if no history records exist, no rollback can be performed.
Be sure to keep that in mind every time you use a table variable, and there is a chance a rollback might happen.
Comments