Forcing MS SQL Server to rollback on error

Question

I am having some problem to understand how transactions work on SQL Server, even after I thought I had all answers from the documentation.

To put it simply, I have a transaction at the beginning of a loong script (which gravely alters the schema), and if any statement fails it should result in a rollback.
On PostgreSQL this works without no problem.

An example to illustrate, on PostgreSQL:

BEGIN TRANSACTION;
DROP TABLE t1;  -- This results in a rollback, because t1 doesn't exist
CREATE TABLE t1 (c1 int); -- This and following statements are never run
COMMIT TRANSACTION;

Example on SQL Server:

SET IMPLICIT_TRANSACTIONS OFF -- 
SET XACT_ABORT ON -- Supposedly stop everything on error
GO
BEGIN TRANSACTION
GO
DROP TABLE t1 -- This results in an error
GO
CREATE TABLE t1 (c1 int) -- This is execute anyway?
GO
COMMIT TRANSACTION
GO

Even though the script results in an error, it never aborts to rollback. And the table "[dbo].[t1]" exist in the schema.

Someone suggested wrapping the statements in a TRY/CATCH block, but this does not work due to some schema alterations requiring be split up in batches. (E.g. renaming/adding columns, and later inserting data).

Many answers here does state that SET XACT_ABORT ON should indeed result in the script to be aborted, but it does not.

What am I missing here?

Answer 1

You should check for existing metadata rather than creating transaction logs with queries such as:

If OBJECT_ID('dbo.t1') is null
begin
    CREATE TABLE t1 (c1 int);
    print 'create';
end
else print 'nothing';

or

if not exists(Select 1 From sys.tables where name = 't1' and SCHEMA_NAME(schema_id) = 'dbo')
begin
    CREATE TABLE t1 (c1 int);
    print 'create';
end
else print 'nothing';

or

If OBJECT_ID('dbo.t1') is not null
begin
    DROP TABLE t1
    print 'drop';
end
else print 'nothing';
CREATE TABLE t1 (c1 int);

It is still recommended to use Transaction and Try/Catch.

Unless you are creating objects such as view, you don't need GO everywhere and this will work:

begin try 
    begin tran
    DROP TABLE t1
    print 'drop';
    CREATE TABLE t1 (c1 int);
    print 'create';
    if @@trancount > 0 commit
    print 'commit'
end try
begin catch
    print 'rollback'
    Print ERROR_MESSAGE()
    if @@trancount > 0 rollback
end catch
select * from t1;

You will still see this error:

Msg 208, Level 16, State 1, Line 17 Invalid object name 't1'.

It is considered as an error in your query because an object does not exist and it will go to the catch block because T1 does not exists. There is no error with the Transaction itself.

Answer 2

There are a couple of issues here.

Firstly, some errors terminate the current statement and some (an inconsistent and rare few) terminate the whole batch.

set XACT_ABORT on makes statement terminating errors become batch aborting errors (which is good because it forces some consistency).

The problem here is that each of these go statements mark the beginning and ending of a batch. When a batch-aborting error occurs, I believe that SQL is reverting to either the start of the batch or the transaction beginning.

Also, because the table create is in a subsequent batch, it is executed just fine.

As mentioned by the other answer, preventing errors is better than detecting them. The drop table should be proceeded by an if statement that checks to see if the table exists before dropping it.

if object_id('dbo.t1') is not null drop table t1;