SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Nakul Vachhrajani / Fun with IDENTITY - Part II / Latest Posts

RE: Fun with IDENTITY - Part II

natalie.ignatieva — Wed, 27 Oct 2010 15:08:20 GMT

Great question, thank you!

RE: Fun with IDENTITY - Part II

deepak.a — Mon, 18 Oct 2010 03:17:21 GMT

thanks for the good question

RE: Fun with IDENTITY - Part II

paul.goldstraw — Mon, 11 Oct 2010 03:16:40 GMT

[quote][b]da-zero (10/11/2010)[/b][hr][quote][b]paul.goldstraw (10/11/2010)[/b][hr]Well it looks like i'm going to be in the minority by saying i think it was a poor question. I got it wrong because I was expecting, funny enough, a question on identity.[/quote]The fact that the titel of the question isn't highly correlated with the question itself, doesn't make it a poor question. I think that this question has alerted many people of the fact that they should be careful with table variables and transactions, thus making it a valuable question (but nothing to do with Identity, true true).[quote][b]paul.goldstraw (10/11/2010)[/b][hr]Sour grapes maybe, but I think questions should be about testing someone's knowledge, not their powers of observation. [/quote]Well, the knowledge tested was table variables vs transactions. It is not really a tricky question because it is not just the @ sign that can tell you it is a table variable, but also the fact that a declare statement is used instead of a create table statement. Someone's observation skills must not be that strong (or reading the question with not enough attention) to miss that.[/quote]No, my observation skills aren't brilliant at 9am on a Monday, but I simply think they shouldn't have to be. Topics have titles and tags describing what they are testing for a reason. This question doesn't test anything at all to do with identities. It's not that it's more about table variables and transactions than identities, it's [i]all[/i] about them. That is the reason why I believe it to be a poor question, not because of the question itself but because in the structure it is set it, I don't think it is unreasonable to expect the topic to bear some relation to the question, which this does not. Just my opinion. Just a shame that the lesson I learned from this was not re. temp tables and table variables but to check whether the question even relates to the topic.

RE: Fun with IDENTITY - Part II

Koen Verbeeck — Mon, 11 Oct 2010 03:03:41 GMT

[quote][b]paul.goldstraw (10/11/2010)[/b][hr]Well it looks like i'm going to be in the minority by saying i think it was a poor question. I got it wrong because I was expecting, funny enough, a question on identity.[/quote]The fact that the titel of the question isn't highly correlated with the question itself, doesn't make it a poor question. I think that this question has alerted many people of the fact that they should be careful with table variables and transactions, thus making it a valuable question (but nothing to do with Identity, true true).[quote][b]paul.goldstraw (10/11/2010)[/b][hr]Sour grapes maybe, but I think questions should be about testing someone's knowledge, not their powers of observation. [/quote]Well, the knowledge tested was table variables vs transactions. It is not really a tricky question because it is not just the @ sign that can tell you it is a table variable, but also the fact that a declare statement is used instead of a create table statement. Someone's observation skills must not be that strong (or reading the question with not enough attention) to miss that.

RE: Fun with IDENTITY - Part II

paul.goldstraw — Mon, 11 Oct 2010 02:45:43 GMT

Well it looks like i'm going to be in the minority by saying i think it was a poor question. I got it wrong because I was expecting, funny enough, a question on identity. The fact that the word identity doesn't appear anywhere in the explanation of the answer backs my opinion up that this was a sort of trick question. I have no problem with testing on the differences between temp tables and table variables and indeed, if the question had said it was related to that i would have got it right.Sour grapes maybe, but I think questions should be about testing someone's knowledge, not their powers of observation. Paul

RE: Fun with IDENTITY - Part II

Kevin Gill — Fri, 08 Oct 2010 10:16:31 GMT

[quote][b]UMG Developer (10/8/2010)[/b][hr]I almost didn't notice, and was think this is exactly the same as the question from a few days ago... [/quote]I only tend to log on and answer a batch once every couple of weeks or so these days, and I end up doing them in reverse order! I got the other one right though :-)

RE: Fun with IDENTITY - Part II

UMG Developer — Fri, 08 Oct 2010 09:42:35 GMT

[quote][b]Kevin Gill (10/8/2010)[/b][hr]Didn't notice it was a table variable! Gah! :-)[/quote]I almost didn't notice, and was think this is exactly the same as the question from a few days ago... So I brought up the old question and compared, and then the light went off and I knew how to answer it. :-)

RE: Fun with IDENTITY - Part II

L' Eomot Inversé — Fri, 08 Oct 2010 05:36:49 GMT

[quote][b]Kevin Gill (10/8/2010)[/b][hr]Didn't notice it was a table variable! Gah! :-)[/quote]Me too. That'll teach us to read more carefully!

RE: Fun with IDENTITY - Part II

Kevin Gill — Fri, 08 Oct 2010 03:20:33 GMT

Didn't notice it was a table variable! Gah! :-)

RE: Fun with IDENTITY - Part II

cengland0 — Fri, 08 Oct 2010 02:33:35 GMT

Good question. I almost missed it because I didn't notice it was loading a table variable at first. I was saved because I remember the previous question that seemed exactly the same and almost answered the same way. I knew there must have been some difference so I looked closer at the query and then noticed it wasn't an actual table but a table variable instead. Whew! Close call on that one.Lesson learned: Pay close attention to the details.

RE: Fun with IDENTITY - Part II

L' Eomot Inversé — Wed, 06 Oct 2010 16:22:30 GMT

Good question.

RE: Fun with IDENTITY - Part II

Jason Shadonix — Wed, 06 Oct 2010 14:47:19 GMT

Nice!

RE: Fun with IDENTITY - Part II

rockvilleaustin — Wed, 06 Oct 2010 11:06:47 GMT

Great question! Learned something new again!

RE: Fun with IDENTITY - Part II

SQLRNNR — Wed, 06 Oct 2010 10:37:00 GMT

Nice Question - Thanks

RE: Fun with IDENTITY - Part II

mtillman-921105 — Wed, 06 Oct 2010 09:46:18 GMT

Terrific question thanks.

RE: Fun with IDENTITY - Part II

webrunner — Wed, 06 Oct 2010 08:56:56 GMT

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

RE: Fun with IDENTITY - Part II

Daniel Bowlin — Wed, 06 Oct 2010 06:58:57 GMT

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

RE: Fun with IDENTITY - Part II

Dhruvesh Shah — Wed, 06 Oct 2010 05:06:18 GMT

Great one....

RE: Fun with IDENTITY - Part II

COOL_ICE — Wed, 06 Oct 2010 03:31:36 GMT

I got it worng no dought it is a good question :-)

RE: Fun with IDENTITY - Part II

Michael Riemer — Wed, 06 Oct 2010 03:11:14 GMT

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.

RE: Fun with IDENTITY - Part II

larsts — Wed, 06 Oct 2010 02:28:18 GMT

Good question! :-)

RE: Fun with IDENTITY - Part II

Koen Verbeeck — Wed, 06 Oct 2010 00:27:01 GMT

Great question! Definately learned something today.

RE: Fun with IDENTITY - Part II

Hardy21 — Wed, 06 Oct 2010 00:24:56 GMT

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

RE: Fun with IDENTITY - Part II

manjul.dube — Tue, 05 Oct 2010 23:36:17 GMT

Good Question.. thanks

RE: Fun with IDENTITY - Part II

Nakul Vachhrajani — Tue, 05 Oct 2010 23:18:42 GMT

[quote][b]UMG Developer (10/5/2010)[/b][hr]Nice question, thanks!Related BOL links: SQL Server 2008 R2: [url]http://msdn.microsoft.com/en-us/library/ms175010.aspx[/url]SQL Server 2008: [url]http://msdn.microsoft.com/en-us/library/ms175010(SQL.100).aspx[/url]SQL Server 2005: [url]http://msdn.microsoft.com/en-us/library/ms175010(SQL.90).aspx[/url]SQL Server 2000: [url]http://msdn.microsoft.com/en-us/library/aa260638(SQL.80).aspx[/url]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 [b]variables[/b] don't participate in transactions, but I never found that.)[/quote]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 "[b]variables[/b] don't participate in transactions" :-)

RE: Fun with IDENTITY - Part II

Dhruvesh Shah — Tue, 05 Oct 2010 23:01:10 GMT

Good Question. I found this link on google which explains it bit more in detailshttp://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

RE: Fun with IDENTITY - Part II

UMG Developer — Tue, 05 Oct 2010 22:39:48 GMT

Nice question, thanks!Related BOL links: SQL Server 2008 R2: [url]http://msdn.microsoft.com/en-us/library/ms175010.aspx[/url]SQL Server 2008: [url]http://msdn.microsoft.com/en-us/library/ms175010(SQL.100).aspx[/url]SQL Server 2005: [url]http://msdn.microsoft.com/en-us/library/ms175010(SQL.90).aspx[/url]SQL Server 2000: [url]http://msdn.microsoft.com/en-us/library/aa260638(SQL.80).aspx[/url]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 [b]variables[/b] don't participate in transactions, but I never found that.)

Fun with IDENTITY - Part II

Nakul Vachhrajani — Tue, 05 Oct 2010 20:46:48 GMT

Comments posted to this topic are about the item [B]Fun with IDENTITY - Part II[/B]