Transactions and Table Variables
Published: 30th November 2014
Now, anyone who’s read enough of my blog will probably know that I’m not too fond of the Table Variable as I see it being used too often and generally in a way that it’s not designed.

Well, this is yet another reason why I’m not a fan.

Basically I hope that the majority of you are all using good error handling in your code and therefore making use of explicit transactions, rollbacks and commits. But did you know that table variables are not actually part of a transaction and therefore are not affected by these?

At this point you’re probably not believing me because the whole point of an explicit transaction is the ability to perform subsequent rollbacks or commits and therefore everything must abide by this logic… think again…

Let’s try a simple example. We’ll declare a table variable, add the number 1 and then we’ll open a transaction and perform 2 updates to this value. One will succeed and the other will fail (due to my using a tinyint).

What we would expect is that the failure will cause the CATCH block to be triggered and the whole transaction be rolled back. Therefore we should end up with just the number 1 left in the table, yes?

declare @table table(id tinyint)
insert into @table select 1

begin try
      
begin transaction
            update
@table set id = 2 -- success

            
update @table set id = 3000 -- failure
      
commit transaction
end
try
begin catch
      
rollback transaction
end
catch

select *
from @table


As you can see, we end up with the number 2. This isn’t right. Why was the 2 committed and not affected by the rollback? Well the table variable simply isn’t part of a transaction.

Let’s try and force the issue by using XACT_ABORT which forces the whole transaction to fail on any error…

set xact_abort on

declare
@table table(id tinyint)
insert into @table select 1

begin try
      
begin transaction
            update
@table set id = 2 -- success

            
update @table set id = 3000 -- failure
      
commit transaction
end
try
begin catch
      
rollback transaction
end
catch

select *
from @table


Nope. As you can see… we have the same issue.

So, just to prove the point, let’s run this again but using a temp table…

set xact_abort on

if
OBJECT_ID('tempDB..#table') is not null drop table #table

create table #table(id tinyint)
insert into #table select 1

begin try
      
begin transaction
            update
#table set id = 2 -- success

            
update #table set id = 3000 -- failure
      
commit transaction
end
try
begin catch
      
rollback transaction
end
catch

select *
from #table


As you can see… this is the behavior that we wanted and expected all along.

Once more… another reason to be VERY careful when using the table variable in your queries.
Comments:
NB: Comments will only appear once they have been moderated.