The table variable transaction catch

STAY IN TOUCH

Get New posts delivered straight to your inbox

  • Madeira Team

The table variable transaction catch

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

#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

The script is pretty simple:

  1. Inserts identical data to a temp table and to a table variable

  2. Opens a transaction

  3. Perform identical DML commands (insert, update, delete) on both tables

  4. 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.

#tablevariable #transaction

JOIN OUR MAILING LIST

CONTACT US

 3 Rapaport St. Kfar Saba, Israel

 +972-9-7400101

  • Google+ - White Circle
  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle