Rolling Back Table Variables

Table variables, love ’em or hate ’em it seems they’re here to stay. Sometimes table variables don’t always follow the rules that we think they should. Sometimes they behave in strange and mysterious ways. Fortunately, they have been improved with SQL Server 2019 (hopefully we’ll see less a few less recompile hints).

What happens if we rollback an explicit transaction with modifications on a table variable? Let’s find out.

DECLARE @GreatIdea TABLE
(
       ID int IDENTITY NOT NULL,
       Phrase nvarchar (64) NOT NULL
);

BEGIN TRANSACTION;

       INSERT INTO @GreatIdea (Phrase)
       VALUES (N'An index would solve this problem. Just include every column.'),
              (N'Yes, I do need to select every column every time.'),
              (N'Yes, I do want to order by all of those columns.'),
              (N'What do you mean we can sort in the application?');

ROLLBACK TRANSACTION;

SELECT * FROM @GreatIdea;

Any ideas? If you guessed the inserts would be rolled back, unfortunately that’s incorrect.

It’s all still there.

Why wasn’t this empty? We didn’t add any rows after the declaration prior to the explicit transaction. When we did add rows, they were rolled back. Since table variables only exist in memory throughout the duration of the batch, nothing needs to be persisted to enforce ACID principles. Steve Jones has an excellent blog post on this behavior available on SQLServerCentral. It seems this behavior is by design, but interesting nonetheless!

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *