Nested Transaction in SQL Server 2012

Nested transaction is nothing but, you can make another transaction without even committing/Rollback previous transaction. With Every begin the value of @@Trancount increases by one and this is counter balanced by commit statement. As shown in image below. But in case of rollback it directly falls back to zero.

 

Lets experiment this;

Assume i have a table “Columns_for_Transaction_Example” having some values in it

Begin Nested Transaction:

here the value of Trancount will become 1. And new value would be like this as shown below

Let’s begin another transaction:

 

now Trancount will change to 2

 

lets check the value in table after the update, as  we can see offset value will become 0.

so now lets commit one by one , As shown in transaction tree figure trancount will decrease one by one ,can’t reduce to zero directly.

similarly if we do rollback,Trancount decreases directly to zero As shown below.