SQL Server allows you to nest multiple transactions but the results of doing so are completely not obvious.
Take the following example…
In this scenario what records do you think got inserted into MyTable? The syntax implies the second insert was rolled back and the first was committed, this however is not what happens. The rollback on the inner transaction rolls back everything all the way up to the top level transaction causing in this example no new records to be inserted. The above query will actually show an error because when it gets to the commit there is no active transaction as it’s already been rolled back.
How about this query…
Again at first glance you would probably assume the inner transaction gets committed and the outer one gets rolled back. However this also inserts zero new records, any inner commits are ignored when there are outer transactions and the final rollback will rollback everything to the top level.
What about named transactions?
Again nothing gets inserted because even though you’ve named what transaction to commit the inner commit is ignored as there is an outer transaction.
With this in mind there is little point to ever nesting transactions and doing so can actually cause damage in making it easy to think something is happening that isn’t.