In a recent Usergroup meet that I attended, someone asked me a simple yet interesting question about how transactions work and whether there is a concept of nested transactions in SQL Server. Although I have discussed this topic before, I believe it is worth revisiting to reinforce the concept for readers who are new to SQL Server.

Let’s dive into the concept of transactions using the script code provided below:

-- Create our database for testing
USE MASTER;
GO
CREATE DATABASE TransactionsDemo;
GO
USE TransactionsDemo;
GO

-- Create a table for testing
CREATE TABLE tbl_SQLAuth (Col1 INT);
GO
SET NOCOUNT ON;
GO

Once the database is created, our next logical step is to create some transactions to test if we can mimic the use of nested transactions.

-- Create an explicit transaction
BEGIN TRAN OuterTransaction;
GO

INSERT INTO tbl_SQLAuth VALUES (1);
GO 10

-- Create an explicit nested transaction
BEGIN TRAN InnerTransaction;
GO

INSERT INTO tbl_SQLAuth VALUES (2);
GO 10

-- Look at the trancount
SELECT @@TRANCOUNT;
GO

In the example above, we started using an explicit transaction called “OuterTransaction” and then inserted values. After that, we created another transaction called “InnerTransaction”. The @@TRANCOUNT will show a value of 2 now.

Now that we know the transaction count is 2, let’s try to rollback the inner transaction alone.

-- Rollback the inner transaction
ROLLBACK TRAN InnerTransaction;
GO

Surprisingly, we receive an error message stating “Cannot roll back InnerTransaction. No transaction or savepoint of that name was found.” This is interesting because we explicitly created a transaction called “InnerTransaction” in our code, but it is being ignored.

However, the following commands work:

-- Works?
ROLLBACK TRAN OuterTransaction;
GO

-- Or the below command works:
ROLLBACK TRAN;
GO

Strangely, the above commands work. If you try to query the table, you will find that it has no rows.

-- What happened?
SELECT COUNT(*) FROM tbl_SQLAuth;

This is an important learning point when working with the ROLLBACK command in SQL Server. There is no concept of a nested transaction in SQL Server. It is crucial to understand this behavior to avoid any confusion.

Now, let’s clean up our testing environment:

-- Cleanup time
USE MASTER;
GO
DROP DATABASE TransactionsDemo;
GO

I am curious to know if any developers out there have faced a similar situation in their environment and learned it the hard way. If you have any experiences or insights to share, please feel free to leave a comment.