SQL Server Interview: If Outer transaction ROLLBACK, what happens to Inner transaction

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

I hope you know that we can create nested transactions in SQL Server. If you are giving a SQL Server developer interview, an interviewer may ask a question like If Outer transaction ROLLBACK, what happens to an Inner transaction in SQL Server.

The interviewer can also write code in paper and can ask a developer to solve it.

You can find the answer in below demonstration so test it yourself.

Create a sample table:

Open outer and inner transaction and insert few sample records:

Check the count of your table:

Now, Rollback your Outer transactions:

Now, Check the count of your table:
Which is 0 now. Although you committed inner transaction for those 1000 records, outer transaction was not committed, so it rollbacked all transactions.