In our previous article, we explained everything about the Transactions with examples. Here, we show you what the Nested Transactions in SQL Server are and how to create them with examples. We will also discuss the Transaction Save Points in SQL Server.
From the below screenshot, you can see, we created a new table to demonstrate the SQL Server Nested Transactions.

Please refer to SQL Transactions article to understand the concept of SQL Server transactions.
Nested Transactions in SQL Server Example 1
In this SQL Server Nested Transactions example, we will use the @@TRANCOUNT to display the number of transactions that occurred at each layer. It is a simple example without any problem.
SELECT 'Before Staring any Transaction', @@TRANCOUNT BEGIN TRANSACTION TRAN1 SELECT 'After Staring First Transaction', @@TRANCOUNT -- Second Transaction Start BEGIN TRANSACTION TRAN2 SELECT 'After Staring Second Transaction', @@TRANCOUNT COMMIT TRANSACTION TRAN2 -- End Of Second Transaction SELECT 'After Commiting the Second Transaction', @@TRANCOUNT COMMIT TRANSACTION TRAN1 SELECT 'After Commiting the First Transaction', @@TRANCOUNT

Let me show you the practical example of this SQL Server Nested Transactions. In this example, we are using two INSERT Statements. One is inside the Main transaction and another one in Nested Transaction.
USE [SQLTEST] GO BEGIN TRANSACTION TRAN1 INSERT INTO [dbo].[Nested Transactions] ([Transaction], [Desription]) VALUES ('Tran1', 'This is Outer Transaction 1') BEGIN TRANSACTION TRAN2 INSERT INTO [dbo].[Nested Transactions] ([Transaction], [Desription]) VALUES ('Tran2', 'This is Inner Transaction 1') COMMIT TRANSACTION TRAN2 COMMIT TRANSACTION TRAN1 SELECT [EmpID], [Transaction], [Desription] FROM [Nested Transactions]

Nested Transactions in SQL Server Example 2
In this example, we are checking what will happen if we use the ROLLBACK TRANSACTION as the nested transaction.
SELECT 'Before Staring any Transaction', @@TRANCOUNT BEGIN TRANSACTION TRAN1 SELECT 'After Staring First Transaction', @@TRANCOUNT BEGIN TRANSACTION SELECT 'After Staring Second Transaction', @@TRANCOUNT ROLLBACK TRANSACTION SELECT 'After Rollback the Second Transaction', @@TRANCOUNT COMMIT TRANSACTION TRAN1 SELECT 'After Commiting the First Transaction', @@TRANCOUNT

As you can see, it is throwing an error message for the Last COMMIT TRANSACTION TRAN1 statement. Because ROLLBACK will rollback all the statements.
You can observe that, after the Rollback executed, no transactions are running.

In this example, we are using two INSERT Statements. One is inside the Main transaction and another one in Nested Transaction.
USE [SQLTEST] GO BEGIN TRANSACTION TRAN1 INSERT INTO [dbo].[Nested Transactions] ([Transaction], [Desription]) VALUES ('Tran3', 'This is Outer Transaction 2') BEGIN TRANSACTION INSERT INTO [dbo].[Nested Transactions] ([Transaction], [Desription]) VALUES ('Tran4', 'This is Inner Transaction 2') ROLLBACK TRANSACTION COMMIT TRANSACTION TRAN1 SELECT [EmpID], [Transaction], [Desription] FROM [Nested Transactions]

From the below screenshot, see that there is no insertion happened.

SQL Nested Transactions Example 3
Here, we are checking what happens if we use the ROLLBACK TRANSACTION as the outer transaction.
SELECT 'Before Staring any Transaction', @@TRANCOUNT BEGIN TRANSACTION SELECT 'After Staring First Transaction', @@TRANCOUNT BEGIN TRANSACTION TRAN2 SELECT 'After Staring Second Transaction', @@TRANCOUNT COMMIT TRANSACTION TRAN2 SELECT 'After Commiting the Second Transaction', @@TRANCOUNT ROLLBACK TRANSACTION SELECT 'After the First Transaction is Rollbacked', @@TRANCOUNT

Let me show you one practical SQL nested transactions example.
USE [SQLTEST] GO BEGIN TRANSACTION INSERT INTO [dbo].[Nested Transactions] ([Transaction], [Desription]) VALUES ('Tran4', 'This is Outer Transaction 4') BEGIN TRANSACTION INSERT INTO [dbo].[Nested Transactions] ([Transaction], [Desription]) VALUES ('Tran5', 'This is Inner Transaction 5') COMMIT TRANSACTION ROLLBACK TRANSACTION SELECT [EmpID], [Transaction], [Desription] FROM [Nested Transactions]
It will rollback everything inside the first transaction and second transaction.

SQL Server Transactions Save Point Example
Let us see the working function of the SQL Transaction save points. In the below code snippet, we have SAVE TRAN, and this command will save the data up to that point.
For example, if there is any disaster happens after that point, or any rollback command executed doesn’t delete data before the SQL Transaction save point.
SELECT 'Before Staring any Transaction', @@TRANCOUNT BEGIN TRANSACTION TRAN1 SELECT 'After Staring First Transaction', @@TRANCOUNT SAVE TRAN TRAN2 SELECT 'Within the Save Transaction', @@TRANCOUNT ROLLBACK TRAN TRAN2 SELECT 'After Rollback th Save Transaction', @@TRANCOUNT COMMIT TRANSACTION TRAN1 SELECT 'After the First Transaction is Commited', @@TRANCOUNT

The practical example of Sql Server Transaction Save Point.
USE [SQLTEST] GO BEGIN TRANSACTION TRAN1 INSERT INTO [dbo].[Nested Transactions] ([Transaction], [Desription]) VALUES ('Tran6', 'This is Outer Transaction 6') SAVE TRANSACTION TRAN2 INSERT INTO [dbo].[Nested Transactions] ([Transaction], [Desription]) VALUES ('Tran7', 'This is Inner Transaction 7') ROLLBACK TRANSACTION TRAN2 COMMIT TRANSACTION TRAN2 SELECT [EmpID], [Transaction], [Desription] FROM [Nested Transactions]
