April 20, 2010 at 2:08 am
Paul White NZ (4/20/2010)
Including the trivial case where the table has one row, if the rows in the table fit on a single page, SQL Server has a perfectly 'efficient' way to retrieve the rows. That is why I said it depends on the number of rows.
Yes, you are right. For a single page, or even just a few dozen pages, scanning the table is fast enough to be called "efficient". I did not consider that when phrasing the question.
April 20, 2010 at 2:29 am
"SQL Server does not have an efficient way to retrieve rows based on a known value for PersonID."
There have obviously been too many trick questions lately, as I thought this was one as well!
SQL Server does have an efficient way to retrieve rows based on a known value for PersonID, and here it is:
create index indPersonID on Persons (PersonID)
😉
April 20, 2010 at 2:43 am
A Good question. I learned something from this.
I've used IDENTITY for ages, assuming its both duplicate-proof and automatically indexed.
I'm glad to have this pointed out to me here rather than in a live production environment.
April 20, 2010 at 4:12 am
I do have second thought about the First part of the question.
As clearly mention "Consider the following table" and table says about the IDENTITY Column!! Hence we can understand that no Alteration in the Table structure can be done or by passing the LAW!
Agreed with the second part 🙂
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 20, 2010 at 5:44 am
Toreador (4/20/2010)
"SQL Server does not have an efficient way to retrieve rows based on a known value for PersonID."There have obviously been too many trick questions lately, as I thought this was one as well!
SQL Server does have an efficient way to retrieve rows based on a known value for PersonID, and here it is:
create index indPersonID on Persons (PersonID)
😉
This was my reasoning also. Just as the first part (duplicates) would require manual intervention to accomplish, so to would the implementation of SQL Server's efficient way to retrieve rows.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 20, 2010 at 7:11 am
Very nice question.
By the way, this is my first post.
Hello SSC!
April 20, 2010 at 7:25 am
Got it wrong. Good question. Anything that makes me read further to understand something in depth is a good thing. QOTD is an excellent place to learn new things in small digestible chunks.
April 20, 2010 at 7:27 am
Tom Brown (4/20/2010)
A Good question. I learned something from this.I've used IDENTITY for ages, assuming its both duplicate-proof and automatically indexed.
I'm glad to have this pointed out to me here rather than in a live production environment.
Thanks, Tom! This is exactly why I submitted this question, because I see this misunderstanding way too often. I am glad I was able to learn you something today!
Toreador (4/20/2010)
"SQL Server does not have an efficient way to retrieve rows based on a known value for PersonID."There have obviously been too many trick questions lately, as I thought this was one as well!
SQL Server does have an efficient way to retrieve rows based on a known value for PersonID, and here it is:
create index indPersonID on Persons (PersonID)
😉
Toreador (and WayneS, who posted a similar comment): I hate trick questions. If you see a question posted by me, you can always assume that it's not a trick question (at least not deliberate;-)).
The question "Which of the following statements are true about this table?" refered to the table as I posted it, not to the table plus some undisclosed other DDL statements. SQL Server will not add this index on its own, so SQL Server has no efficient way to retrieve rows based on a known value for PersonID. We can give SQL Server such an efficient way, but that would require additional DDL.
free_mascot (4/20/2010)
I do have second thought about the First part of the question.As clearly mention "Consider the following table" and table says about the IDENTITY Column!! Hence we can understand that no Alteration in the Table structure can be done or by passing the LAW!
I'm not sure if I understand what you are saying, free_mascot. Neither DBCC CHECKIDENT, nor IDENTITY_INSERT alter the table structure in any way.
flai, dbowlin: Thanks for the kind words. And a hearty welcome to SSC for flai!
April 20, 2010 at 7:38 am
Good question. This one made me think. I'm impressed that I got it right!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
April 20, 2010 at 7:54 am
Hugo Kornelis (4/20/2010)
Tom Brown (4/20/2010)
A Good question. I learned something from this.I've used IDENTITY for ages, assuming its both duplicate-proof and automatically indexed.
I'm glad to have this pointed out to me here rather than in a live production environment.
Thanks, Tom! This is exactly why I submitted this question, because I see this misunderstanding way too often. I am glad I was able to learn you something today!
I got this question right, but was very afraid of getting called "wrong", because I've seen that assumption about IDENTITY a lot. If I'd just looked at who wrote the question, I'd have had no fear!
It's a pity, too, because you can just add a unique index constraint to the IDENTITY column to get the behavior you want.
April 20, 2010 at 7:56 am
Hugo Kornelis (4/20/2010)
dimitri.decoene-1027745 (4/20/2010)
Moreover, a part of the microsoft online help was a bit misleading on the subject:This is because the IDENTITY property is guaranteed to be unique only for the table on which it is used.
How should I interpret "identity property" in this sentence?
To answer the actual question first - you can interpret it exactly as you did; it refers to the property that is assigned by adding "IDENTITY" to a column in a CREATE or ALTER TABLE statement.
I had to google for the page where you took the quote from to see it in its context. I found it at http://msdn.microsoft.com/en-us/library/ms191131.aspx; if you found it somewhere else please give me a link and I'll review it.
I would not go as far as to say that the text there is incorrect, but it is incomplete and might cause confusion. What the IDENTITY property does give you is a "limited" uniqueness guarantee. Values generated by the IDENTITY property will be unique, as long as you never tamper with them. The two ways of tampering I know of are IDENTITY INSERT and DBCC CHECKIDENT ... RESEED (therer might be more, so please don't take this as an authorative complete list). As long as you never tamper with the IDENTITY values in any way, you can rely on the uniqueness of generated IDENTITY values. At least, as long as you limit your scope to a siingle table; as soon as multiple tables are involved, IDENTITY values will no longer be unique - and that last sentence is what the quoted fragment from the online help is trying to tell you.
I hope this clarifies the confusion.
I got the choice about row retrieval correct because I realized no primary key or other index was specified. So no confusion there. But I got the identity choice wrong.
My confusion regarding IDENTITY INSERT was clarified as soon as I saw the correct answer and the explanation. I guess my confusion (while answering the question) revolved around not realizing that duplicate values can be forced into an identity column if no primary key or unique constraint is specified, which is why the second answer choice is phrased, in part, "duplicate values for PersonID might still occur."
This was a good question. It taught me something about identity columns that I didn't know I didn't know, if you know what I mean. 🙂
Thanks,
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
April 20, 2010 at 8:13 am
Excellent question!!! I like the ones that appear easy on the surface but make you think before you answer.
With all the nitpicking lately I am surprised that nobody complained that the table definition has the last line commented out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 20, 2010 at 8:33 am
Should have read the question carefully. Whenever I use an IDENTITY column, that's for the sole purpose of making it unqiue & a PK; and assumed so here too. The answers looked too easy and I looked at the author's name and thought "why such a simple question". Good question 🙂
April 20, 2010 at 8:36 am
Hi, Hugo! and thanks for your QotD (which, despite my best efforts, I got right :-)). Just wanted to note (and I'm shocked, SHOCKED, that with 27 posts thus far in this discussion, no one yet noted this) something which I trust everyone would agree is really true: your CREATE TABLE will fail with a syntax error because you commented out the right parentheses and listed the last column with a comma!
All the best from Michael
April 20, 2010 at 8:39 am
Just wanted to note (and I'm shocked, SHOCKED, that with 27 posts thus far in this discussion, no one yet noted this) something which I trust everyone would agree is really true: your CREATE TABLE will fail with a syntax error because you commented out the right parentheses and listed the last column with a comma!
I did just a couple post ago. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 16 through 30 (of 66 total)
You must be logged in to reply to this topic. Login to reply