There are plenty of posts out there talking about the differences and disadvantages/advantages of using table variable vs temp table in a T-SQL query. The one thing I wanted to illustrate here is the difference on how they behave when a transaction rollback is involved. In short, transaction involving a table variable cannot be rollback whereas in a temp table it can. Here are two examples I tried to prove that:

Example 1 – in-memory table variable

declare @tranTest table (rowId int identity(1,1), rowVal tinyint)

begin try
begin transaction

insert into @tranTest (rowVal) values (200)
insert into @tranTest (rowVal) values (240)
insert into @tranTest (rowVal) values (256)

commit transaction

end try

begin catch
declare @errorMsg nvarchar(4000), @errorSeverity int, @errorState int;

select
@errorMsg=ERROR_MESSAGE(), @errorSeverity=ERROR_SEVERITY(), @errorState=ERROR_STATE();

raiserror (@errorMsg, @errorSeverity, @errorState);
rollback transaction;
end catch
set nocount on
select * from @tranTest

As the tinyint can only holds integer up to 255, inserting 256 throws an exception; since all three inserts are in a transaction, naturally I thought that select statement from @tranTest would return no records; but I was wrong! Actually, it returned:

rowId rowVal
1 200
2 240

Example 2: transaction in temp table

create table #tranTest (rowId int identity(1,1), rowVal tinyint)

begin try
begin transaction

insert into #tranTest (rowVal) values (200)
insert into #tranTest (rowVal) values (240)
insert into #tranTest (rowVal) values (256)
commit transaction
end try

begin catch
declare @errorMsg nvarchar(4000), @errorSeverity int, @errorState int;

select
@errorMsg=ERROR_MESSAGE(), @errorSeverity=ERROR_SEVERITY(), @errorState=ERROR_STATE();

raiserror (@errorMsg, @errorSeverity, @errorState);
rollback transaction;
end catch
set nocount on

select * from #tranTest
drop table #tranTest

Guess what will be returned? Yes, no record; as this time the transaction does what it is supposed to do – rollback all inserts when any of them fails in the transaction.

In conclusion, do not use in-memory table variable if transaction roll back is needed in case of operation error.