Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Fun with IDENTITY - Part II Expand / Collapse
Author
Message
Posted Tuesday, October 5, 2010 8:46 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:05 AM
Points: 1,453, Visits: 1,856
Comments posted to this topic are about the item Fun with IDENTITY - Part II

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #998910
Posted Tuesday, October 5, 2010 10:39 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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.)
Post #998940
Posted Tuesday, October 5, 2010 11:01 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 17, 2013 11:53 AM
Points: 623, Visits: 237
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
Post #998950
Posted Tuesday, October 5, 2010 11:18 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:05 AM
Points: 1,453, Visits: 1,856
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
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #998960
Posted Tuesday, October 5, 2010 11:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 6, 2010 5:13 AM
Points: 19, Visits: 3
Good Question.. thanks
Post #998968
Posted Wednesday, October 6, 2010 12:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 5:13 AM
Points: 1,131, Visits: 1,394
Thanks 'UMG Developer' for reference links & Nakul for good question.

Thanks
Post #998999
Posted Wednesday, October 6, 2010 12:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:15 AM
Points: 13,635, Visits: 11,504
Great question! Definately learned something today.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #999000
Posted Wednesday, October 6, 2010 2:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 1:36 AM
Points: 173, Visits: 120
Good question!
Post #999052
Posted Wednesday, October 6, 2010 3:11 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, December 15, 2014 8:05 AM
Points: 1,876, Visits: 518
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.
Post #999073
Posted Wednesday, October 6, 2010 3:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 6:10 AM
Points: 350, Visits: 429
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
Post #999085
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse