DDL commands in SQL Server with Examples for Snapshot Isolation Level

By: Sergey Gigoyan | Updated: 2021-02-04 | Comments | Related: > Transactions

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:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-02-04