Are CREATE TABLE statements in SQL Server immune to ROLLBACK?

Question

I have a database that I "release" weekly, meaning I post a backup people can start from, and I post an update script they can use to upgrade from last week's (so they can keep their current data, as much as possible). This script of course contains lots of DDL — CREATE TABLE, ALTER TABLE and so forth. The basic structure of it is like this:

/*

HOW TO USE THIS SCRIPT

1.  Run it against your existing DB
2.  Check whether there were any errors
3.  If there were, issue a rollback by highlighting this:
        ROLLBACK
    and executing it
4.  If there weren't, issue a commit by highlighting this:
        COMMIT
    and executing it
5.  !!! Not doing either of these will leave a transaction open, which will
    probably cause all further queries to time out till you do !!!

*/

SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;
GO

-- Boilerplate checking and whatnot goes here
GO

-- Guts of operation, part 1
GO

-- Guts of operation, part 2
GO

-- Guts of operation, part 3
GO

-- . . .
GO

-- Guts of operation, part N
GO

-- Boilerplate cleanup stuff here
GO

You'll note that I left the transaction open and told them to deal with finishing it manually, depending on what happened. I'd rather have this be automatic, but despaired of it given that the whole thing is always a long series of several batches, and TRY blocks can't span batches, of course. Thus the recent addition of SET XACT_ABORT ON; to ease the pain slightly. Regardless, I've tried this myself since without it and it makes no difference to the scenario. Anyway.

Recently, one of these scripts had statements to create tables and other statements to add check constraints to existing tables. One of my users ran the script and hit an error on the constraint; turns out he had preexisting data that violated the constraint. Ok, no problem, do a ROLLBACK. Right, didn't need to do one, XACT_ABORT already did that. Go off and fix data rows… done. Now to try again! Uhp… what? No error on the constraint this time, but it errors out on the CREATE TABLE statements, saying the tables already exist…! Huh? Didn't it roll back?

We ended up restoring from a backup and re-fixing the data and re-running. But that's neither here nor there.

So, dear readers: How did the creation of those tables survive the rollback of the transaction? How can I get them not to?

---

EDIT: Ok, here's an example you can run.

USE tempdb;
GO

CREATE DATABASE example;
GO

USE example;
GO

CREATE TABLE foo (a INT);
GO

INSERT INTO foo
VALUES (100);
GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;
GO

ALTER TABLE foo ADD CHECK (a < 10);-- Gives error "The ALTER TABLE statement conflicted with the CHECK constraint…", as expected
GO

CREATE TABLE bar (b INT);
GO

ROLLBACK;-- Gives error "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."  Huh?  Where did our transaction go?
GO

SELECT *
FROM bar;-- Gives no error.  Table still exists!  NOT expected!
GO

USE tempdb;
GO

DROP DATABASE example;

Answer 1

SQL Server has an unfortunate error handling behavior. Depending on the specific error it sometimes aborts statement, batch, transaction and connection, or some, or none. This is a very error-prone model to program against.

In your case the batch and transaction were aborted because of XACT_ABORT. But GO is the batch separator. You have multiple batches. Later batches continue to run.

Use one batch, or make later batches check if the previous batch ran (maybe by checking @@TRANCOUNT).

(A better model for SQL Server would be to rollback the transaction but keep it open and make all future statements fail. That would leave the rest of the script included in the transaction and let nothing leak out. SQL Server does not have such a feature.)