Problem
It is possible to include DDL (Data Definition Language) statements in a SQL Server transaction. This means that we can CREATE, DROP or ALTER a table or an index inside a transaction. Then, depending on the logic, we can rollback the work or commit the work and make these objects a permanent part of the database. In one of my articles, we discussed using DDL commands in transactions and I recommend that you read the article before moving on to this material. However, when we use the snapshot transaction isolation level, there are some exceptions. In this tutorial, we are going to discuss DDL statement behavior inside a transaction when using the snapshot isolation level.
Solution
Let’s start by demonstrating the standard behavior of DDL statements inside a transaction.
DDL Commands in SQL Server with Examples
First, we will create a test environment:
USE master
GO
CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE TABLE TestTable
(
ID INT,
Val CHAR (1)
)
INSERT INTO TestTable(ID, Val)
VALUES (1,'A'), (2,'B'),(3, 'C')
Now, let’s issue a DDL statement inside a transaction (we use the default isolation level which is Read Committed):
USE TestDB
GO
BEGIN TRANSACTION
DROP TABLE TestTable
ROLLBACK
SELECT * FROM TestTable
We issue the DROP TABLE command (which is a DDL statement) inside the transaction and then roll it back. As we can see, it works without any issues and the table exists with all of its initial data:
In the next example, we will use the same statement inside a transaction in the snapshot isolation level. To do this, we need to enable the snapshot isolation for the database:
USE master
GO
ALTER DATABASE TestDB SET ALLOW_SNAPSHOT_ISOLATION ON
We start a transaction with the snapshot isolation level and include the previous DDL statement into it:
USE TestDB
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
DROP TABLE TestTable
ROLLBACK
SELECT * FROM TestTable
We can see that the result is the same:
In the next example, we will not rollback, but commit our changes. In the code below, create a table and insert data into it (DDL and DML statements) inside a transaction with the snapshot isolation level. Then, commit these changes:
USE TestDB
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
CREATE TABLE TestTable2 (ID INT, Val INT)
INSERT INTO TestTable2(ID, Val) VALUES(1, 10)
COMMIT
SELECT * FROM TestTable2
The result shows that the table is successfully created and the data is inserted:
Up to this point, all DDL statements that we have issued inside transactions in the snapshot isolation level work without any issues. What if we want to add a column to our table and create an index on one of the columns with a transaction in the snapshot isolation level in SQL Server? The next piece of code attempts to do that:
USE TestDB
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
ALTER TABLE TestTable2 ADD Val2 INT
CREATE UNIQUE INDEX UX_TestTable2_ID ON TestTable2 (ID)
COMMIT
An error message appears, and our transaction fails.
As is mentioned in the error message below, these DDL statements are not allowed inside a transaction with the snapshot isolation level in SQL Server. First, let’s attempt to understand the error message and which statements are not allowed inside snapshot isolation transactions. As versioning of the metadata is not supported in SQL Server, there are some DDL statements that are not allowed in explicit transactions with snapshot isolation level. According to Microsoft, these statements are ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, and common language runtime (CLR) DDL statements. We used ALTER TABLE and CREATE INDEX statements in our last transaction and now it is clear why our transaction failed. Having said that, it is important to mention that the statements cannot be used inside an explicit transaction. However, these statements can be used implicitly in a transaction running under snapshot isolation level. The example below illustrates this behavior.
USE TestDB
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
ALTER TABLE TestTable2 ADD Val2 INT
CREATE UNIQUE INDEX UX_TestTable2_ID ON TestTable2 (ID)
The transaction runs under the snapshot isolation level, but there is no BEGIN TRANSACTION statement. Therefore, there is no explicit transaction. The statements are issued separately, like independent, implicit transactions. In this case, the statements are successfully completed and the column and index are added:
Conclusion
While SQL Server allows us to include DDL statements inside transactions, there are some exceptions in the case of transactions running with the snapshot isolation level in SQL Server. There is a list of DDL statements that cannot be included in explicit transactions running under the snapshot isolation level. Nevertheless, these statements can be issued implicitly even under the snapshot isolation level.
Next Steps
For additional information, please use the following links:
