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.