• 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