atomic my ass

gistfile1.txt

create table Demo (
   Number int not null primary key
)
go
create proc wtf as
   begin tran
   insert demo values(1)
   insert demo values(1)
   commit tran

go
create proc wtf2 @level int as
  set xact_abort on
  begin tran
  insert demo values(1)
  raiserror('sorry dave', @level, 1)
  commit tran

go
create proc wtf3 @level int as
   set xact_abort on
   begin try
      begin tran
      insert Demo values(1)
	  raiserror('sorry dave', @level, 1)
	  commit tran
   end try
   begin catch
      print 'caught error'
      rollback tran
   end catch

--what if a statement fails within a transaction?
delete demo
exec wtf
if exists(select * from demo) print 'BROKEN'

--what if we raise an error (level < 10) when xact_abort on?
delete demo
exec wtf2 10
if exists(select * from demo) print 'BROKEN'

--what if we raise an error (level > 10) when xact_abort on?
delete demo
exec wtf2 11
if exists(select * from demo) print 'BROKEN'

--what if we raise an error and rollback in the catch block?
delete demo
exec wtf3 10
 if exists(select * from demo) print 'BROKEN'
 select * from demo

How atomic is SQL Server? You would expect that a transaction is rolled back when there are errors.