Are CREATE TABLE statements in SQL Server invulnerable to ROLLBACK?

Question

I have a database that I “release” weekly, which means that I am sending a backup copy that people can start with, and I am posting an update script that they can use to update from last week (so that they can save their current data as possible). This script, of course, contains a lot of DDL - CREATE TABLE , ALTER TABLE and so on. Its main structure is as follows:

 /* 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 will notice that I left the transaction open and told them to complete it manually, depending on what happened. I would prefer it to be automatic, but despair of him, given that all this is always a long series of several parties, and TRY blocks cannot cover parties, of course. Thus, the recent addition of SET XACT_ABORT ON; relieves pain. Regardless, I tried it myself, since without it, and it does not matter for the script. Anyway.

Recently, one of these scenarios had instructions for creating tables and other statements to add control constraints to existing tables. One of my users ran a script and hit a restriction error; it turns out that he had pre-existing data that violates the restriction. Well, no problem, do a ROLLBACK . That's right, there was no need to do this, XACT_ABORT already did it. Delete and correct the data lines ... done. Now try again! Uhp ... what? This time there is no restriction error, but there are errors in the CREATE TABLE statements saying that the tables already exist ...! A? Didn't he roll back?

We have finished restoring from the backup and reinstalling the data and re-executing. But it is neither here nor there.

So, dear readers: How were these tables created to roll back a transaction? How can I make them not do this?

---

EDIT: Alright, here is 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 failed error handling behavior. Depending on the specific error, the statement, package, transaction, and connection are sometimes interrupted, or some or not. This is a very error prone programming model.

In your case, the batch and transaction were aborted due to XACT_ABORT . But GO is a batch separator. You have several parties. Later parties continue to work.

Use one batch or complete later batch if the previous batch was performed (possibly by checking @@TRANCOUNT ).

(The best model for SQL Server would be to roll back the transaction, but keep it open and make all future statements fail. This would leave the remainder of the script included in the transaction and prevent any leaks. Do not have this capability.)