Transactions and Table Variables
Published: Aug 14, 2016
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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron