Question of the month September 2015? – Why is table variable not affected by rollback?

Consider the following code

declare @t table(id int)
begin transaction
insert into @t (id) select 100
rollback transaction
select id from @t

When you execute the above code, the result is 100.

Why is table variable not affected by rollback?

 

 

 

Advertisements

5 thoughts on “Question of the month September 2015? – Why is table variable not affected by rollback?

  1. Hello Madhivanan,

       As the table variable is temporary storage ,it's values does not the final/permanent database values,means table variable does not affect the database values permanently. And as per the ACID rule of database,when there is final affection to database then rollback will be applied to it.
    

    Same concept will be applied to local variable or temporary storage also like

    declare @t VARCHAR( 20)=’ABC’
    SELECT @t
    begin TRANSACTION
    SET @t=’XYZ’
    rollback transaction
    select @t

    The above code has the variable with the initial value=’ABC’ and during transaction it is set to ‘XYZ’ but when we rollback that transaction then it is not affected by rollback command.

    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s