Fun with IDENTITY - Part II

Question

Post reply

Fun with IDENTITY - Part II

Nakul Vachhrajani

SSChampion

Points: 10294
More actions
October 5, 2010 at 8:46 pm

#227588

Comments posted to this topic are about the item Fun with IDENTITY - Part II
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins

Viewing 15 posts - 1 through 15 (of 27 total)

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

UMG Developer SSChampion Points: 13482 More actions · Answer 1

Nice question, thanks!

Related BOL links:

SQL Server 2008 R2: http://msdn.microsoft.com/en-us/library/ms175010.aspx

SQL Server 2008: http://msdn.microsoft.com/en-us/library/ms175010(SQL.100).aspx

SQL Server 2005: http://msdn.microsoft.com/en-us/library/ms175010(SQL.90).aspx

SQL Server 2000: http://msdn.microsoft.com/en-us/library/aa260638(SQL.80).aspx

All of them say:

* "Transactions involving table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources."

* "... because table variables have limited scope and are not part of the persistent database, they are not impacted by transaction rollbacks."

I think that pretty much says that table variables don't fully participate in transactions, and can't be rolled back. (I was expecting to find something that said variables don't participate in transactions, but I never found that.)

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 2

Good Question.

I found this link on google which explains it bit more in details

http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 3

UMG Developer (10/5/2010)
Nice question, thanks!
Related BOL links:
SQL Server 2008 R2: http://msdn.microsoft.com/en-us/library/ms175010.aspx
SQL Server 2008: http://msdn.microsoft.com/en-us/library/ms175010(SQL.100).aspx
SQL Server 2005: http://msdn.microsoft.com/en-us/library/ms175010(SQL.90).aspx
SQL Server 2000: http://msdn.microsoft.com/en-us/library/aa260638(SQL.80).aspx
All of them say:
* "Transactions involving table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources."
* "... because table variables have limited scope and are not part of the persistent database, they are not impacted by transaction rollbacks."
I think that pretty much says that table variables don't fully participate in transactions, and can't be rolled back. (I was expecting to find something that said variables don't participate in transactions, but I never found that.)

Thanks for your support and providing the great links.

I believe I could not get the supporting documentation for this question because I was looking for a reference to the fact that "variables don't participate in transactions" 🙂

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

manjul.dube Grasshopper Points: 23 More actions · Answer 4

manjul.dube

Grasshopper

Points: 23

October 5, 2010 at 11:36 pm

#1232244

Good Question.. thanks

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 5

Thanks 'UMG Developer' for reference links & Nakul for good question.

Thanks

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 6

Great question! Definately learned something today.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

larsts SSC Enthusiast Points: 183 More actions · Answer 7

larsts

SSC Enthusiast

Points: 183

October 6, 2010 at 2:28 am

#1232304

Good question! 🙂

Michael Riemer SSCertifiable Points: 5190 More actions · Answer 8

Great question and thanks for the reference documentation. Nearly missed the fact that you were loading a table variable!!!

It is something I have used myself on occasions to manage logging, if we do a standard rollback, the logging is rolled back as well, so tracing records was impossible unless we logged to a table variable in the transaction. Once the transaction is rolled back or commited, the logging is stored into a fixed table.

COOL_ICE SSCommitted Points: 1706 More actions · Answer 9

I got it worng no dought it is a good question 🙂

- SAMJI
If you marry one they will fight with you, If you marry 2 they will fight for you 🙂

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 10

Dhruvesh Shah

SSCrazy

Points: 2303

October 6, 2010 at 5:06 am

#1232369

Great one....

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 11

Table variables don't participate in transactions.....hmmm.....I suppose that makes sense. I definitely learned something today. Thanks.

webrunner SSC-Dedicated Points: 31869 More actions · Answer 12

Thanks for the question. I got it wrong but learned a valuable lesson about table variables.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 13

Terrific question thanks.

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

Nice Question - Thanks

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events