Nested Transactions means putting one transaction inside of another – According to BOL if you have one transaction statement ‘nested’ inside another,

“ Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.”

Let us look at practical usage of this statement and also with @@trancount variable which is supposed to show how many transactions are open.All examples are from Adventureworks.

Scenario 1: This is a very regular scenario of two nested transactions and both being committed.

BEGIN TRANSACTION 
  UPDATE Person.Contact SET FirstName = 'GustavO' WHERE ContactID = 1 
  SELECT @@TRANCOUNT—returns 1 
  BEGIN TRANSACTION 
    UPDATE Person.Address SET AddressLine1 = '1950 Napa Ct' WHERE AddressID = 1 
    SELECT @@TRANCOUNT—returns 2 
    COMMIT TRANSACTION 
    SELECT FirstName FROM Person.Contact WHERE ContactID = 1 
    SELECT [AddressLine1] FROM Person.Address WHERE AddressID = 1 
    --Both are updated!!

    SELECT @@TRANCOUNT—reduced to 1 
COMMIT TRANSACTION

SELECT @@TRANCOUNT--0 
SELECT FirstName FROM Person.Contact WHERE ContactID = 1 
SELECT [AddressLine1] FROM Person.Address WHERE AddressID = 1

Both the updates are committed with the first commit transaction itself, all that the second commit does is to set the @@trancount to 0.

Scenario 2: Trying what BOL says, to commit ‘inner transaction’ and rollback the ‘outer one’ -

BEGIN TRANSACTION 
  UPDATE Person.Contact SET FirstName = 'GustavG' WHERE ContactID = 1 
  SELECT @@TRANCOUNT--1 
  BEGIN TRANSACTION 
    UPDATE Person.Address SET AddressLine1 = '1190Napa Ct' WHERE AddressID = 1 
    SELECT @@TRANCOUNT--2 
    COMMIT TRANSACTION 
    SELECT FirstName FROM Person.Contact WHERE ContactID = 1 
    SELECT [AddressLine1] FROM Person.Address WHERE AddressID = 1 
    --Both transactions are committed 
    SELECT @@TRANCOUNT--0 
ROLLBACK TRANSACTION --rolls back both transactions 
SELECT @@TRANCOUNT--0 
SELECT FirstName FROM Person.Contact WHERE ContactID = 1 
SELECT [AddressLine1] FROM Person.Address WHERE AddressID = 1

Both the updates are rolled back with the second rollback and the @@trancount is set to be 0.

Scenario 3: Trying the opposite, rollback ‘inner’ transaction and commit the ‘outer one’- this is not quite the same, this rolls back both transactions and gives an error for the outer one.

BEGIN TRANSACTION 
  UPDATE Person.Contact SET FirstName = 'GustavH' WHERE ContactID = 1 
  SELECT @@TRANCOUNT--1 
  BEGIN TRANSACTION 
    UPDATE Person.Address SET AddressLine1 = '1191 Napa Ct' WHERE AddressID = 1 
    SELECT @@TRANCOUNT--2 
    ROLLBACK TRANSACTION 
    SELECT FirstName FROM Person.Contact WHERE ContactID = 1 
    SELECT [AddressLine1] FROM Person.Address WHERE AddressID = 1 
    --Both transactions are rolled back!! 
    SELECT @@TRANCOUNT--0 
COMMIT TRANSACTION --gives error that transaction does not exist 
SELECT @@TRANCOUNT--0 
SELECT FirstName FROM Person.Contact WHERE ContactID = 1 
SELECT [AddressLine1] FROM Person.Address WHERE AddressID = 1

From the above tests it is clear that

1 @@ trancount is set to 0 whenever there is a ROLLBACK.

2 When there is a COMMIT TRANSACTION @@trancount is decremented by 1.

3 Nested transactions are affected by a single commit or rollback. In other words if commit or rollback statements are used with no transaction name, they affect all the 'nested' transactions.  This is what leads to the argument that 'nested transaction' is only a term and there is no such thing in reality.

Let us look at same behavior with named transactions.

Scenario 1:

BEGIN TRANSACTION tran1
  UPDATE Person.Contact SET FirstName = 'GustavB' WHERE ContactID = 1
  SELECT @@TRANCOUNT--1
  BEGIN TRANSACTION tran2
    UPDATE Person.Address SET AddressLine1 = '1100 Napa Ct' WHERE AddressID = 1
    SELECT @@TRANCOUNT--2 
    COMMIT TRANSACTION tran2
    SELECT FirstName FROM Person.Contact WHERE ContactID = 1
    SELECT [AddressLine1] FROM Person.Address WHERE AddressID = 1
    --Both transactions are committed
    SELECT @@TRANCOUNT--1
COMMIT TRANSACTION tran1
--Decrements @@trancount
SELECT @@TRANCOUNT--0

This is similar to the situation with no named transactions.

Scenario 2:

BEGIN TRANSACTION tran1
  UPDATE Person.Contact SET FirstName = 'GustavB' WHERE ContactID = 1
  SELECT @@TRANCOUNT--1
  BEGIN TRANSACTION tran2
    UPDATE Person.Address SET AddressLine1 = '1100 Napa Ct' WHERE AddressID = 1
    SELECT @@TRANCOUNT--2 
    COMMIT TRANSACTION tran2
    SELECT FirstName FROM Person.Contact WHERE ContactID = 1
    SELECT [AddressLine1] FROM Person.Address WHERE AddressID = 1
    --Both transactions look committed
    SELECT @@TRANCOUNT--1
ROLLBACK TRANSACTION tran1
--Decrements @@trancount, all changes rolled back
SELECT @@TRANCOUNT--0

This is also similar to the situation with no named transactions - the behavior of the outermost transaction is what matters regardless of the inner one.

Scenario 3:

BEGIN TRANSACTION tran1
  UPDATE Person.Contact SET FirstName = 'GustavB' WHERE ContactID = 1
  SELECT @@TRANCOUNT--1
  BEGIN TRANSACTION tran2
    UPDATE Person.Address SET AddressLine1 = '1100 Napa Ct' WHERE AddressID = 1
    SELECT @@TRANCOUNT--2 
    ROLLBACK TRANSACTION tran2
    SELECT FirstName FROM Person.Contact WHERE ContactID = 1
    SELECT [AddressLine1] FROM Person.Address WHERE AddressID = 1
    --Both transactions are committed
    SELECT @@TRANCOUNT--2
COMMIT TRANSACTION tran1
--Decrements @@trancount - both transactions are committed
SELECT @@TRANCOUNT--1

In this situation - the inner 'rollback' gives an error stating 'Cannot roll back tran2. No transaction or savepoint of that name was found.' This further confirms our understanding that there are no 'nested transactions' in reality, only one transaction really exists that can be committed or rolled back.

References:

http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2630)-nested-transactions-are-real.aspx