DDL commands in SQL Server with Examples for Snapshot Isolation Level
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.
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:
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.
For additional information, please use the following links:
- DDL commands in Transactions in SQL Server versus Oracle
- Comparison of SQL Server Serializable and Snapshot isolation levels
- READ_COMMITTED_SNAPSHOT and SNAPSHOT_ISOLATION levels in SQL Server
- Transaction Locking and Row Versioning Guide
About the author
View all my tips
Article Last Updated: 2021-02-04