Nested Transaction? No such thing!

My post about transaction isolation levels has prompted me to have a good old rant about one part of the T-SQL syntax that drives me mad is the ability to create nested transactions, despite the fact that no such thing exists in SQL! Don't believe me? Check this out...

CREATE TABLE NestedTransactions
(Col1 int)

BEGIN TRAN --Outer Transaction
     INSERT INTO NestedTransactions
     VALUES(1)
     BEGIN TRAN --Inner Transaction
          INSERT INTO NestedTransactions
          VALUES(2)
     COMMIT TRAN --Inner Transaction
ROLLBACK --Outer Transaction

SELECT * FROM NestedTransactions

...So in the script above, we have committed the inner transaction, so even though we rollback the outer transaction, the NestedTransaction table should still include the value 2 from the inner transaction, right? Wrong...

...Ok, so what about if we try this script...

BEGIN TRAN --Outer Transaction
     INSERT INTO NestedTransactions
     VALUES(1)
     BEGIN TRAN --Inner Transaction
          INSERT INTO NestedTransactions
          VALUES(2)
     ROLLBACK --Inner Transaction
COMMIT --Outer Transaction

SELECT * FROM NestedTransactions

...This time, we have rolled back the inner transaction, but committed the outer transaction, so the NestedTransactions table should have one value, of 1, right? Wrong...

...And check out the message I received...

(1 row(s) affected)
(1 row(s) affected)
Msg 3902, Level 16, State 1, Line 9
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
(0 row(s) affected)
...As you can see, when it rolled back the transaction, it rolled back everything, so when I tried to commit the outer transaction, there was nothing to commit.

The bottom line, is that nested transactions do not exist!

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom