Why rollback is not working for variable table in SQL Server 2012?

Question

I have created one variable table. In my stored procedure, there are lots of transactions.

Now whenever an error occurs, I want to rollback a specific transactions which has some statements which insert or update or delete records from variable table.

This is just an example of my actual problem :

declare @tab table (val int)

insert into @tab select 2
insert into @tab select 3
insert into @tab select 4

select * from @tab

begin tran
begin try
    update @tab set val = 1
    select 1/0;
    commit
end try
begin catch
    rollback
end catch

select * from @tab

Actual output :-

My expected output is :-

So here rollback of a transaction is not working. Why it is not working here ? Am I doing something wrong ?

Answer 1

You are not using a temp table, you are using a variable table. There is a difference.

Temp tables work with transactions, variable tables don't. See http://blog.sqlauthority.com/2009/12/28/sql-server-difference-temp-table-and-table-variable-effect-of-transaction/

If you were to change your variable table @tab to a temporary table of #tab, you would get your desired behavior.

Differences between temp and variable tables: https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

I have modified my question. Thanks for your knowledge sharing. But question remains the same. Why it is not working for variable table?

The links I posted above go through that with more detail than I could.