Do table variables log records in transaction log?

Question

Post reply

Do table variables log records in transaction log?

m--S3qU3L

Hall of Fame

Points: 3706
More actions
April 8, 2010 at 12:51 am

#146405

Do table variables log records in transaction log?
Contradicting informations in two articles:
http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx
http://www.mssqltips.com/tip.asp?tip=1556
What happens in case of a ROLLBACK.
John

Viewing 4 posts - 1 through 3 (of 3 total)

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

Joffin Mathew SSC Enthusiast Points: 105 More actions · Answer 1

In my experience, Table variable will not rollback.

A table variable's lifespan is only for the duration of the transaction that it runs in.

Joffin Mathew SSC Enthusiast Points: 105 More actions · Answer 2

Table Variables are not bound to any transaction as they are just like any other variable.

Just like any other variable, a Table Variable's scope exists only within the context of the current level.

Unlike Temp Tables, it is not accessible to sub levels (of Stored Procedures)

If we execute the DECLARE statement first, then attempt to insert records into the '@Table_Variable' table variable we receive the error because the table variable has passed out of existence.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

And, no... table variables are not logged. That's part of the reason why they aren't/can't be rolled back.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)