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 ««12345»»»

Using IDENTITY as a key column Expand / Collapse
Author
Message
Posted Tuesday, April 20, 2010 1:02 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, June 15, 2012 12:48 AM
Points: 176, Visits: 306
Although I knew of the possibility to reseed and explicitly adding identity values to a table, I never considered the fact that it made it possible to add non-unique values for the identity column within one table.

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?
Post #906580
Posted Tuesday, April 20, 2010 1:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 5,930, Visits: 8,179
Thanks for the feedback, to all involved in this discussion so far.

I appreciate all the kind words and compliments. And I'l try to address the questions and criticism in the rest of this post.

Paul White NZ (4/19/2010)
I am less sure about the wording in the second part of the question, "SQL Server does not have an efficient way to retrieve rows based on a known value for PersonID." because that rather depends on the number of rows in the table.

I don't think it would have been giving too much away to say something like "SQL Server cannot use an index to locate rows based on a known value for PersonID".

I wanted to disagree with this at first, but since several other people have posted similar comments, I'll have to accept that, obviously, this was not clear enough.

However, I don't understand the argument that this depends on the number of rows in the table. Can you explain for what number of rows a SELECT based on WHERE PersonID = @PersonID will be faster without an index on PersonID, and why?

ST_John (4/20/2010)
Hang on a minute ...

"Even though the IDENTITY property is intended to be used for key columns, SQL Server will not automatically generate a constraint to enforce the uniqueness, so duplicate values (caused by the methods above) will not be banned from the table. SQL Server will also not automatically create an index to speed up access based on the IDENTITY value (though it will do so when you add the PRIMARY KEY or UNIQUE constraint to enforce uniqueness of the IDENTITY column, as it does for each PRIMARY KEY or UNIQUE constraint). "

the question asked if SQL server had an efficient way to query records, not if it would use that method automatically!


Everything you write is true, but I don't really see the point you try to make. Since the CREATE TABLE statement as given will not create any indexes, there is no efficient access path to query records based on known values for PersonID; only an index will enable those. As long as it's impossible to query efficiently, the question of whether the efficient way will indeed be used is immaterial.

tommyh (4/20/2010)
The heading said "Using IDENTITY as a key column". Key part there being "key". Now combined with the fact that the create table statement was incomplete "-- other columns);". Got me wondering if the author had a missing "primary key (PersonID)" at the end (or something similar). Or that is was implied that there would be code to create a key. Which of course would have inverted the answer.


The comment said "-- (other columns)", not "- (other columns and constraints)". Assuming that I meant to include constraints when I wrote columns is quite a long stretch.
The reaason I chose the title "Using IDENTITY as a key column" is because this is exactly the pattern I do sometimes see - tables with an IDENTITY attribute, with queries that use this column as if it were the key, but no constraints to enforce that key. I guess I could also have chosen the title "Using IDENTITY as a key column without enforcing its uniqueness", but I'm afraid that would have given the question away. Annyway, I'm sorry if the chosen title confused you. My intention was to confuse people with believable but incorrect answer options, and nothing else

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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #906587
Posted Tuesday, April 20, 2010 1:44 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, June 15, 2012 12:48 AM
Points: 176, Visits: 306
Thank you for the reply Hugo, that article was indeed the source of the quote.
Post #906597
Posted Tuesday, April 20, 2010 1:45 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 4:48 AM
Points: 210, Visits: 1,102
CirquedeSQLeil (4/19/2010)

It is in reference to the default of SQL Server creating an index on that column. For Primary key columns, an index is auto-generated - but that is not true of an identity column, unless the identity column is a part of a Primary key.


It was the new thing to learn, but at the cost of loss of 2 valuable points.
bad luck... ! & an average question... !

Thanks
Post #906598
Posted Tuesday, April 20, 2010 2:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 AM
Points: 11,192, Visits: 11,098
Hugo Kornelis (4/20/2010)
I wanted to disagree with this at first, but since several other people have posted similar comments, I'll have to accept that, obviously, this was not clear enough.

Clear enough, possibly...I did after all understand what you were getting at...I just felt the wording could have been improved, and suggested an alternative.

However, I don't understand the argument that this depends on the number of rows in the table. Can you explain for what number of rows a SELECT based on WHERE PersonID = @PersonID will be faster without an index on PersonID, and why?

My difficulty is with the phrase "SQL Server does not have an efficient way to retrieve rows..." - I never said it could be faster without an index (though it could).

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.

I suppose I could argue that if the table were filled with very many rows having the same value for PersonID, an IAM-ordered scan of the heap might be more efficient than a partial scan of a very fragmented index, but that was not my original point...

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #906606
Posted Tuesday, April 20, 2010 2:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 5,930, Visits: 8,179
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #906610
Posted Tuesday, April 20, 2010 2:29 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:52 AM
Points: 1,714, Visits: 6,257
"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)

Post #906624
Posted Tuesday, April 20, 2010 2:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:27 PM
Points: 1,293, Visits: 1,429
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.
Post #906631
Posted Tuesday, April 20, 2010 4:12 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #906686
Posted Tuesday, April 20, 2010 5:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:04 PM
Points: 6,582, Visits: 8,861
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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #906728
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse