Whoa! @table variables unaffected by Transaction

  • 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!

  • Actually, this is stated in BOL.

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

  • 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!

  • 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

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply