Introduction

One common question that many new developers have about SQL Server concerns the ability of varioius DDL statements to be rolled back. These are the CREATE, ALTER, DROP, TRUNCATE, and other data definition language (DDL) statements that are executed against various SQL Server objects.

Since development often involves changes to schema objects, it can be important to ensure that a complete set of changes are made or that all changes are undone. In this article, we will examine and show how the CREATE statements are affected by transactions. We will look at examples of each type of statement and then devle into batches of multiple statements.

CREATE Default

The CREATE DDL command is used for any number of objects. We often associate this with tables, views, stored procedures, and functions. However, if you look through the Table of Contents (left side of the CREATE TABLE link above), you will see that users, triggers, keys, sequences, types, and more are used with CREATE. 

One note is that some batches need the CREATE statement to be the first statement, so I've included GOs in the code. This is not always the case, but it makes it easier to see the code with batch separators. 

Let's test a simple set of code for a default. If I create one in a transaction, and roll back the transaction, does the object continue to exist? Here's some sample code:

SELECT *
 FROM sys.objects
 WHERE name = 'dfOne'
GO
BEGIN TRANSACTION createdefault
GO
CREATE DEFAULT dfOne AS 1
GO
SELECT *
 FROM sys.objects
 WHERE name = 'dfOne'
go
ROLLBACK TRANSACTION createdefault
GO
SELECT *
 FROM sys.objects
 WHERE name = 'dfOne'
 

If we execute this code, we see the results below. The object doesn't initially exist, but is created inside the transaction. However, once the rollback completes, there is no object.

This technique works for many types of objects, but not all. Now let's look at how we can rollback multiple changes

Trapping Errors

As an example, let's create a view and a procedure, one of which will not work correctly. Here is the code I'll use.

CREATE TABLE #errors
(   errornumber    INT
  , errorstatement VARCHAR(100));
GO
BEGIN TRANSACTION
GO
CREATE VIEW MyView
AS
SELECT mj.JobID, mj.mydb, mj.startdate
 FROM dbo.MyJobs AS mj
GO
IF @@ERROR <> 0
    INSERT #errors VALUES (1, 'Create MyView');
GO
CREATE FUNCTION GetOne (@val INT)
RETURNS int
AS
BEGIN
  INSERT MyTable 
END;
go
IF @@ERROR <> 0
    INSERT #errors VALUES (1, 'Create GetOne');
GO
IF EXISTS (SELECT * FROM #errors AS e)
  SELECT * FROM #errors AS e
  ROLLBACK;
ELSE
  COMMIT
go
DROP TABLE #errors;
 

When I run this, I'm creating a temp table at the beginning. I use this to store information between batches. When the CREATE VIEW runs, if an error occurs, this is still set for the next batch, as the GO doesn't execute. This means that I can read the value and insert a row into the temp table. The same thing occurs with the CREATE PROCEDURE, which does produce an error. My IF statement at the end checks if any errors have occurred and then rolls back the transaction if they have. If not, I commit the code.

My results when running this are:

Note that in the Results pane, I actually see where the error occurred.

In a real deployment situation where I'd want to have logging, I'd be sure to capture the output of my temp table for debugging purposes.

I haven't shown the queries for sys.objects, but these objects were not created and the changes rolled back.

Trying CREATE Table

Let's examine this in a more common form with a table. I'll execute this code to create a table, add some data, and then query it inside of a transaction. Note, there is no commit of the transaction.

BEGIN TRANSACTION createtable
GO
CREATE TABLE dbo.SQLServerVersions (VersionName VARCHAR(50))
INSERT dbo.SQLServerVersions (
    VersionName
)
VALUES
  ('SQL Server 2008' )
, ('SQL Server 2008 R2' )
, ('SQL Server 2012' )
, ('SQL Server 2014' )
, ('SQL Server 2016' )
, ('SQL Server 2017' )
GO
SELECT * FROM dbo.SQLServerVersions
GO

When I do this, I have these results. I see all the rows from the table.

Now let's rollback the transaction. Once I do that, I get these results, and as you can see, the table doesn't exist.

This is completely expected. Our DDL here is contained in a transaction, and if there are errors or problems, we want to ensure that the changes are rolled back.

Memory Optimized Tables

Things are different here. I don't know how many of you use Memory-Optimized tables, though I bet it's relatively few of you. For those of you that do, you are probably aware of this.

Memory-Optimized Tables cannot participate in a transaction. If you attempt to do this, you will receive an error message, as shown below.

This does mean that you don't have a good method of altering your schema with both Memory-Optimized (MOT) objects and other objects. If you need to ensure that changes occur to both types of objects, you will need to write your own error trapping and removal of the MOT objects. My recomendation is that you make all your non-MOT changes and trap errors. You can then make each MOT change and decide whether to undo this or leave it, based on changes with other objects. 

One good thing to consider is checking the status of changes made to non MOT objects succeed and then using this to determine whether to undo changes to the MOT objects.

Conclusion

We have shown how different types of objects respond to transaction rollbacks. In the case of many objects, we need to use multiple batches to submit changes. This requires that we have some method of trapping errors and a method of committing or rolling back changes. Memory Optimized objects bring with them special challenges, but they can be combined with other DDL changes if you write more extensive code that will undo changes.

Handling transactions around your DDL, often used to deploy updates to your database, is a bit cumbersome. I've rarely done this as much of the time I've used other tooling, such as SQL Compare, which handles the complete delpoyment or rollback for me. You can write this code yourself, but be sure you extensively test it before you actually depend on it to catch errors that might exist in your code.

https://www.sqlservercentral.com/Forums/1970843/DDL-statements-getting-rollbacked