Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.