Whoa! @table variables unaffected by Transaction

• Lowell

  SSC Guru

  Points: 323518

  More actions

  September 26, 2007 at 9:02 am

  #181214

  I was making an example for someone to demonstrate how a transaction rollback does not rollback the identity() of an insert...and i discovered that a table variable is not affected by the transaction. It's not the behavior I expected, so i just thought i'd mention it to all. it's probably because memory items can't be logged, but interesting.

  try this: it's the same whether 2000 or 2005:

  --unaffected by transaction

  set nocount on

  declare @sample table (sampleid int identity(1,1), sampletext varchar(30) )

  insert into @sample(sampletext) values ('one')

  insert into @sample(sampletext) values ('two')

  select * from @sample

  begin tran

  insert into @sample(sampletext) values ('three')

  insert into @sample(sampletext) values ('four')

  select * from @sample

  rollback tran

  insert into @sample(sampletext) values ('five, not three')

  select * from @sample

  --affected as expected by trasnaction

  set nocount on

  CREATE TABLE #sample (sampleid int identity(1,1), sampletext varchar(30) )

  insert into #sample(sampletext) values ('one')

  insert into #sample(sampletext) values ('two')

  select * from #sample

  begin tran

  insert into #sample(sampletext) values ('three')

  insert into #sample(sampletext) values ('four')

  select * from #sample

  rollback tran

  insert into #sample(sampletext) values ('five, not three')

  select * from #sample

  drop table #sample

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 26, 2007 at 9:05 am

  #736413

  Actually, this is stated in BOL.

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  September 26, 2007 at 9:07 am

  #736416

  Just to complete your answer : why do you expect a rollback to work when there's nothing logged in the transactions log?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  September 26, 2007 at 9:26 am

  #736426

  yeah i agree; it's just my knee jerk reaction was "but it's in a TRANSACTION", like that makes up for everything.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• MladenPrajdic

  Old Hand

  Points: 395

  More actions

  September 26, 2007 at 10:11 am

  #736444

  this feature is acctually very usefull in auditing.

  for example if you have a need to rollback the data in a trigger or in a sproc but you have to

  save the data you changed before a rollback.

  look here:

  http://weblogs.sqlteam.com/mladenp/archive/2007/04/13/60174.aspx

  _______________________________________________

  Causing trouble since 1980

  blog: http://weblogs.sqlteam.com/mladenp

  SSMS Add-in that does a few things: www.ssmstoolspack.com