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:
1
2
|
CREATE TABLE tbl_TestTrans(ID INT)
GO
|
Open outer and inner transaction and insert few sample records:
1
2
3
4
5
6
7
8
|
BEGIN TRANSACTION OuterTrans
GO
BEGIN TRANSACTION InnerTrans
GO
INSERT INTO tbl_TestTrans VALUES(88)
GO 1000
COMMIT TRANSACTION InnerTrans
GO
|
Check the count of your table:
1
2
3
4
|
SELECT COUNT(1) FROM tbl_TestTrans
GO
--The result is:
-- 1000
|
Now, Rollback your Outer transactions:
1
2
|
ROLLBACK TRANSACTION OuterTrans
GO
|
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.
1
2
3
4
|
SELECT COUNT(1) FROM tbl_TestTrans
GO
--The result is:
-- 0
|
