Forcing MS SQL Server to rollback on error 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?
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 &gt; 0 commit
    print 'commit'
end try
begin catch
    print 'rollback'
    Print ERROR_MESSAGE()
    if @@trancount &gt; 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.