InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 17:34:35 GMT20http://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspxI agree. I'm somewhat new to this site, but poorly worded questions sometimes take more thought to deduce the writer's meaning than just answering or researching the material... :-) I see that even some of the long-timers are getting burned by them occasionally.Sat, 02 Oct 2010 17:26:58 GMTrtelgenhoffhttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]Hugo Kornelis (5/7/2010)[/b][hr]And if you never use the identity column in queries, then the best way to improve performance is to drop that unused column.[/quote]What if it's only there to provide a short UID for child tables to reference? You'd never use the column to access the row, but you'd read it to access the child rows.[/quote]Then you'd be using the column in queries, right? And those queries would benefit from the index on the identity column.In fact, even if you'd only use the IDENTITY for a FOREIGN KEY constraint and never use it to actually combine referenced and referencing row (which, in my eyes, would be a purely theoretical scenario), you'd still benefit from the index, as without it a table scan has to be used to enforce the FOREIGN KEY constraint.Fri, 07 May 2010 04:06:07 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]Hugo Kornelis (5/7/2010)[/b][hr]If you define the identity column as a PRIMARY KEY, then there always is an index. SQL Server automaticallly creates an index when you create a PRIMARY KEY constraint, and uses that index to enforce the constraint.[/quote]D'oh! :ermm:[quote][b]Hugo Kornelis (5/7/2010)[/b][hr]And if you never use the identity column in queries, then the best way to improve performance is to drop that unused column.[/quote]What if it's only there to provide a short UID for child tables to reference? You'd never use the column to access the row, but you'd read it to access the child rows.Fri, 07 May 2010 03:58:42 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]Toreador (5/7/2010)[/b][hr][quote][b]vignesh 38804 (5/7/2010)[/b][hr]Index on identity column improves performance[/quote]If you never use the identity column in queries but only use it as a primary key then doesn't it actually make performance slightly worse?[/quote]If you define the identity column as a PRIMARY KEY, then there always is an index. SQL Server automaticallly creates an index when you create a PRIMARY KEY constraint, and uses that index to enforce the constraint.And if you never use the identity column in queries, then the best way to improve performance is to drop that unused column.Fri, 07 May 2010 03:50:50 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]vignesh 38804 (5/7/2010)[/b][hr]Index on identity column improves performance[/quote]If you never use the identity column in queries but only use it as a primary key then doesn't it actually make performance slightly worse?Fri, 07 May 2010 02:54:54 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspxIndex on identity column improves performanceFri, 07 May 2010 01:54:37 GMTvignesh 38804http://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspxAn iffy question IMHO. You need to make certain assumptions to come to the right/wrong answer. Are the points being returned? :-)Mon, 03 May 2010 05:32:25 GMTChiragNShttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]Pooja-462303 (4/29/2010)[/b][hr]Can you pls give me an instance/how the identity column can have duplicate.[/quote]Sure!Using SET IDENTITY_INSERT ON:[code="sql"]CREATE TABLE Demo1 (Id int NOT NULL IDENTITY, Other varchar(20) );INSERT INTO Demo1 (Other)VALUES ('First');SET IDENTITY_INSERT Demo1 ON;INSERT INTO Demo1 (Id, Other)VALUES (1, 'Another first?');SELECT * FROM Demo1;DROP TABLE Demo1;[/code]Using DBCC CHECKIDENT with RESEED:[code="sql"]CREATE TABLE Demo2 (Id int NOT NULL IDENTITY, Other varchar(20) );INSERT INTO Demo2 (Other)VALUES ('First');DBCC CHECKIDENT (Demo2, RESEED, 0);INSERT INTO Demo2 (Other)VALUES ('Another first?');SELECT * FROM Demo2;DROP TABLE Demo2;[/code]Thu, 29 Apr 2010 16:01:11 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspxI am really surprised,I thought I was a newbie but now i have to call myself lower than newbie, Can you pls give me an instance/how the identity column can have duplicate.Thank youThu, 29 Apr 2010 15:48:19 GMTPooja-462303http://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]Hugo Kornelis (4/22/2010)[/b][hr]Do you think you can post a repro script that actually uses this operator? Have you ever seen it used in your code?[/quote]Yes, sometimes I see this operator when I profile and optimize SQL queries. When I wrote that post, I was optimizing a long-running stored procedure, and there even were both lazy and eager index spools in one query. I tried to make a simplified example (the original query contained 3 tables, 1 view, and 2 subqueries), but all what I got was either hash join or table spool :-)Thanks to Paul for posting a simple example!Tue, 27 Apr 2010 01:47:37 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]Hugo Kornelis (4/22/2010)[/b][hr]Thanks, vk-kirov. I was not aware of this operator.From the limited information I could find on it, it will be used if the same data is used at least twice in a query, the second needs only a subset of the rows needed for the first (otherwise a normal spool would be used), and the subset is based on a predicate that is sargable and selective enough to earn back the cost of creating the index.Do you think you can post a repro script that actually uses this operator? Have you ever seen it used in your code?[/quote]You can generate an Index Spool easily with an unindexed Tally table:[code="sql"]SELECT NINTO #TallyFROM dbo.Numbers (50000);GOSELECT COUNT(*)FROM #Tally T1JOIN #Tally T2 ON T2.N < T1.N;[/code]That uses an in-line TVF I keep handy to generate a temporary tally heap - you can use any method that generates a similar object.Index spools are not all that rare, but the estimated cost of spooling the rows to a worktable, creating the index, and dropping it afterward is quite high, so the optimiser will often choose a different approach, purely on cost grounds (usually a hash join - the example above avoids that by joining on an inequality only).There are two types of index spool - eager and lazy. The above example generates an eager spool - one that consumes all rows from its input before it can be used to seek with.A lazy index spool is a bit more like a regular table spool - except it never truncates its worktable (a normal spool only ever caches one result - it truncates its worktable on every rebind).A lazy index spool accumulates results on every rebind (when the correlated parameter changes). Personally, I'd love a way to be able to hint the use of lazy index spools, but that's another story.Interestingly, you cannot trust the reported numbers for rebinds and rewinds for an index spool - the internals report a rewind to anything other than the last value seen as a rebind rather than the rewind it actually is. Sad, but true.[img]http://www.sqlservercentral.com/Forums/Attachment5740.aspx[/img]Thu, 22 Apr 2010 05:26:38 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]vk-kirov (4/22/2010)[/b][hr][quote][b]Hugo Kornelis (4/20/2010)[/b][hr]If SQL Server had an auto-tune feature that automatically adds indexes it beliefs will benefit performance, than you would be right. But no current version of SQL Server has such a feature (and based on recommendations I sometimes see from the DTA, that might as well be for the best).[/quote]Sometimes SQL Server automatically creates temporary indexes if the optimizer finds out that it will improve query performance.Ref: Nonclustered Index Spool Showplan Operator [url]http://msdn.microsoft.com/en-us/library/ms189611.aspx[/url]Of course, the index spool operation will not happen in the statement 'SELECT * FROM Persons WHERE PersonID = @PersonID', but it may happen in some statement with many JOINs. And of course, it would be less effective than explicit index creation on PersonID and using that index :-)[/quote]Thanks, vk-kirov. I was not aware of this operator.From the limited information I could find on it, it will be used if the same data is used at least twice in a query, the second needs only a subset of the rows needed for the first (otherwise a normal spool would be used), and the subset is based on a predicate that is sargable and selective enough to earn back the cost of creating the index.Do you think you can post a repro script that actually uses this operator? Have you ever seen it used in your code?Thu, 22 Apr 2010 02:28:24 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]Hugo Kornelis (4/20/2010)[/b][hr]If SQL Server had an auto-tune feature that automatically adds indexes it beliefs will benefit performance, than you would be right. But no current version of SQL Server has such a feature (and based on recommendations I sometimes see from the DTA, that might as well be for the best).[/quote]Sometimes SQL Server automatically creates temporary indexes if the optimizer finds out that it will improve query performance.Ref: Nonclustered Index Spool Showplan Operator [url]http://msdn.microsoft.com/en-us/library/ms189611.aspx[/url]Of course, the index spool operation will not happen in the statement 'SELECT * FROM Persons WHERE PersonID = @PersonID', but it may happen in some statement with many JOINs. And of course, it would be less effective than explicit index creation on PersonID and using that index :-)Thu, 22 Apr 2010 01:59:01 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]pjdiller (4/21/2010)[/b][hr]I think it's been demonstrated more than a few times (every time?) that the original question is best as a conversation-starter. :)Thanks first to Hugo and then to the rest![/quote]Agree {we need a smiley update, no "thumbs-up" :angry: } Even for question where I get the answer right, going through the conversations make me realize certain intricacies involved that I didn't think through. Thanks to all for those conversations.Wed, 21 Apr 2010 08:22:07 GMTrjv_rnjnhttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspxSpeaking (typing really) as someone who was initially annoyed that they got the question wrong, I'd like to now say that I'm happy with the overall result.I think it's been demonstrated more than a few times (every time?) that the original question is best as a conversation-starter. :)Thanks first to Hugo and then to the rest!Wed, 21 Apr 2010 07:55:42 GMTpjdillerhttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]Toreador (4/21/2010)[/b][hr]...although I was (I think) the first person to comment on the answer to the second part...[/quote]Nope! :-DWed, 21 Apr 2010 04:13:48 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspxI'd just like to make clear that although I was (I think) the first person to comment on the answer to the second part, I was reflecting negatively on myself, and not the question (which was fine)!If you apply common sense rather than obsessive pedantry* then it's pretty obvious what's meant, isn't it?* that's another negative comment against myself, not an attack on others :)Wed, 21 Apr 2010 03:55:51 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]Glenn Dorling (4/20/2010)[/b][hr]So for the first part you're assuming [u]explicit[/u] behaviour but for the second you're assuming [u]implicit[/u] behaviour.[/quote]Glenn, I think that's a stretch, and an artificial distinction to make. The first part was entirely clear and accurate, in my personal view.Wed, 21 Apr 2010 02:39:02 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]Glenn Dorling (4/20/2010)[/b][hr]Sorry Hugo, but I have to add my voice to the nay-sayers.For the first part of the question your answer is based on the fact that [u]by default[/u] SQL Server will not allow duplicate IDs, but [u]explicitly[/u] a duplicate ID can be created.However, for the second part of the question you're arguing things the other way around. [u]By default[/u] SQL Server will not add an index, but there is an [u]explicit[/u] way to provide an efficient query plan.So for the first part you're assuming [u]explicit[/u] behaviour but for the second you're assuming [u]implicit[/u] behaviour.By the way though, I agree with the basic premise of the question. There's many myths around identity fields (can never have duplicates, can never have gaps, ...). My "favourite" that we get tripped on far too often is when developers (especially vendors) don't think about what happens when the maximum field value is reached.[/quote]If multiple people say the same, then there has to some truth in it.When I submitted the question, I thought that the words "duplicate values for PersonID [b][i]might [/i][/b]still occur" (not "will occur" or so) would imply that this part of the question is whether it is possible or not, whereas the words "SQL Server [b][i]does not have[/i][/b] an efficient way" (not "might be given an efficient way" or so) would imply that that part of the question is about access based on the table posted.Apparently, this was not clear to all. My apologies to all who got confused because of this, and I hope you still learned something from this question.Wed, 21 Apr 2010 02:22:56 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspxSorry Hugo, but I have to add my voice to the nay-sayers.For the first part of the question your answer is based on the fact that [u]by default[/u] SQL Server will not allow duplicate IDs, but [u]explicitly[/u] a duplicate ID can be created.However, for the second part of the question you're arguing things the other way around. [u]By default[/u] SQL Server will not add an index, but there is an [u]explicit[/u] way to provide an efficient query plan.So for the first part you're assuming [u]explicit[/u] behaviour but for the second you're assuming [u]implicit[/u] behaviour.By the way though, I agree with the basic premise of the question. There's many myths around identity fields (can never have duplicates, can never have gaps, ...). My "favourite" that we get tripped on far too often is when developers (especially vendors) don't think about what happens when the maximum field value is reached.Tue, 20 Apr 2010 18:44:27 GMTGlenn Dorlinghttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]Hugo Kornelis (4/20/2010)[/b][hr]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.[/quote]That depends on the number of rows in the table.I thought we covered this point already :-PTue, 20 Apr 2010 18:14:21 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]pjdiller (4/20/2010)[/b][hr]Now, I'm not convinced that this is the last word, but I wanted to describe the cause for my confusion. How do I create an insert statement that will get past an error like this without changing the default behavior by turning IDENTITY_INSERT to ON? (because it appears that I would have to intentionally turn that on in order to "break the rules" based on my little test)[/quote]That is what you would do. The identity would still be present on the table, but you would still be able to insert a duplicate value into the table.Tue, 20 Apr 2010 15:52:11 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspxFirst, before I go on, let me say that I respect my place here as a "junior member." I appreciate what goes on here, and I consider this my main source for real information beyond BOL.Having said that, I am now a bit confused about this IDENTITY question. I created a table in SQL08.[code="sql"]CREATE TABLE [dbo].[LOGON_AUDIT1]( [Audit_ID] [int] IDENTITY NOT NULL, [Login_Name] [varchar](255) NULL, [Client_Host] [varchar](255) NULL, [Application_Name] [varchar](255) NULL, [Post_Time] [datetime] NULL )[/code]then I inserted a row.[code="sql"]INSERT INTO [LOGON_AUDIT1] ([Login_Name] ,[Client_Host] ,[Application_Name] ,[Post_Time]) VALUES ('joe' ,'joe''s host' ,'joe''s app' ,GETDATE())[/code]You won't be surprised by the result.I then ran this insert, attempting to create a new row with ID 1, which is already used.[code="sql"]INSERT INTO [LOGON_AUDIT1] (Audit_ID, [Login_Name] ,[Client_Host] ,[Application_Name] ,[Post_Time]) VALUES (1, 'joe' ,'joe''s host' ,'joe''s app' ,GETDATE())[/code]I received an error:Msg 544, Level 16, State 1, Line 1Cannot insert explicit value for identity column in table 'LOGON_AUDIT1' when IDENTITY_INSERT is set to OFF.Now, I'm not convinced that this is the last word, but I wanted to describe the cause for my confusion. How do I create an insert statement that will get past an error like this without changing the default behavior by turning IDENTITY_INSERT to ON? (because it appears that I would have to intentionally turn that on in order to "break the rules" based on my little test)Tue, 20 Apr 2010 14:53:03 GMTpjdillerhttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspxOnly if you first set IDENTITY_INSERT to ON; the default setting for IDENTITY_INSERT is OFF. . .Tue, 20 Apr 2010 14:38:10 GMTJeff Cook-476310http://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]pjdiller (4/20/2010)[/b][hr]Yeah, not a fan of the question. Seriously flawed IMHO.SQL does have an efficient way... no one is arguing with that.Identity is going to auto increment right, it won't allow duplicates... oh unless you tell it to break its own rules. You could also tweak the file using notepad... but why would you want to do that? :)[/quote]It will only prevent duplicates if you have a unique key or primary key on the identity column. An identity column allows you to insert any int value into the field (when defined as int as an example) regardless of the current seed and increment. If you wanted to, you could easily insert 200 records all with the same value for that identity column - if there is no unique key or primary key on it.Tue, 20 Apr 2010 14:08:19 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspxYeah, not a fan of the question. Seriously flawed IMHO.SQL does have an efficient way... no one is arguing with that.Identity is going to auto increment right, it won't allow duplicates... oh unless you tell it to break its own rules. You could also tweak the file using notepad... but why would you want to do that? :)Tue, 20 Apr 2010 14:01:58 GMTpjdillerhttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspxHugo responded:[quote]In this case, the code runs (the comma is actually not permitted by a strict interpretation of Books Online, but the parser happens to be forgiving in this case).[/quote]True, the parser is forgiving of the comma, but if it's part of a pre-2008 installation, it will be less forgiving of the last column's type ;-).Tue, 20 Apr 2010 14:01:26 GMTMichael Poppershttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspxI see a lot of new comments have been posted while I was away. Thanks all for the feedback! I think that there's too many of them to address every comment individually, so I'll try to address all the major concerns in one post.First, [b]thanks[/b] for the many kind words and compliments. I liked them all, especially this one:[quote][b]webrunner (4/20/2010)[/b][hr]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. :-)[/quote]It is my firm belief that, while the things you know you don't know can be a nuisance, the things you don't know you don't know are the real danger - so I am very pleased with this feedback!On to the more, ahem, constructive;-) feedback.[quote][b]SeanLange (4/20/2010)[/b][hr]I am surprised that nobody complained that the table definition has the last line commented out.[/quote]Sean, and Michael who posted a similar remark - you are both right. I have no idea how this happened. My normal indenting style for CREATE TABLE is to have the closing parenthesis on a line of its own. So this is how the CREATE TABLE statement [b]should[/b] have looked:[code="sql"]CREATE TABLE Persons (PersonID int NOT NULL IDENTITY, FirstName nvarchar(40) NOT NULL, MiddleInitial char(1) NULL, LastName nvarchar(40) NOT NULL, DateOfBirth date NULL,-- other columns );[/code]In this case, the code runs (the comma is actually not permitted by a strict interpretation of Books Online, but the parser happens to be forgiving in this case).How I ended up moving the closing parenthesis to the preceeding (commented) line, I don't know. My apologies for the lack of proofreading.[quote][b]JT-361866 (4/20/2010)[/b][hr]The first part of the question was fine, but the second "SQL Server does not have an efficient way to retrieve rows based on a known value for PersonID." is not only poorly worded but the answer is just plain wrong.SQL Server DOES IN FACT HAVE an efficient way to retrieve rows based on a known value for PersonID.Here are three ways to increase the efficiency of retrieving rows based on known values for the PersonID column1. Adding a Primary Key to that column2. Adding a Unique Index or Unique Constraint to that column3. Adding a general Index based on the PersonID columnThe question should have read something like this:"By default SQL Server does not have an efficient way to retrieve rows based on a known value for PersonID."[/quote]The question was "which of the following statements are true about [i]this table[/i]" [emphasis added], not "which of the following statements are true about this table plus some other DDL". If SQL Server had an auto-tune feature that automatically adds indexes it beliefs will benefit performance, than you would be right. But no current version of SQL Server has such a feature (and based on recommendations I sometimes see from the DTA, that might as well be for the best).[quote][b]skjoldtc (4/20/2010)[/b][hr]Man, oh man. I have been told my soooo many experienced DBAs and trainers that an IDENTITY will NOT be duplicated under any conditions. This is a myth that gets promulgated by trainers and those who should know better. And, it's a very dangerous one. Good thing I put a unique constraint when I use IDENTITY. Thanks for this Hugo. I just got a brainstorm (and boy, does it hurt) that someone should write an article about SQL Server myths.[/quote]My first reaction would be to go and find some better trainers. There's plenty of them around who will never tell you nonsense like that. And my second reaction would be to send those trainers my way (looking for an [i]evil[/i] smiley, but can't find one)As to the brainstorm - do an internet search on "sql server myth" and you'll see that there are plenty of articles on that already. :-)Tue, 20 Apr 2010 13:51:30 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspxHugo, Great question. Made me think and analyze since in the "old days" the only way to regenerate the identity key was to drop the table and recreate it then reload the data.It is a shame that there are those who want to rewrite the question to make their answer right. JT, the question was pretty specific and if one reads the answer it does apply to the question. Maybe grammer is a little off, but it is a great question.Tue, 20 Apr 2010 12:33:55 GMTsjimmohttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspxGood question, Hugo!I would change "Because of the IDENTITY attribute, no duplicate values for PersonID will occur" to "Because of the IDENTITY attribute, no duplicate values for PersonID [b][i]can[/i][/i][/b] occur."The first statement may or may not be true depending on whether someone overrides the default INSERT behavior so checking the box for that choice may or may not be correct. However, the second statement is definitely not true.Thanks. . .Tue, 20 Apr 2010 11:26:04 GMTJeff Cook-476310http://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspxVery intelligent question. I got this wrong though :(. The second part of the question tricked me into thinking that the SQL Server will have an efficient way to retrieve rows based on the IDENTITY column since records are arranged sequentially, however this is not true coz there is no clustered index or PK constraint defined on the PersonID.Tue, 20 Apr 2010 10:55:58 GMTAmolNaikhttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]Michael Poppers (4/20/2010)[/b][hr]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 [u]really[/u] 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 [i]Michael[/i][/quote]If you take that code and rewrite it as such:[code="sql"]CREATE TABLE Persons (PersonID int NOT NULL IDENTITY, FirstName nvarchar(40) NOT NULL, MiddleInitial char(1) NULL, LastName nvarchar(40) NOT NULL, DateOfBirth date NULL,);[/code]Note that the comma following the last column is still present. SQL Server will automatically ignore this last comma and the command will still complete successfully. However, the commenting of the closing parenthesis will cause an error.Tue, 20 Apr 2010 10:11:48 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]WayneS (4/20/2010)[/b][hr][quote][b]Toreador (4/20/2010)[/b][hr]"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 [i]does [/i]have an efficient way to retrieve rows based on a known value for PersonID, and here it is:create index indPersonID on Persons (PersonID);-)[/quote]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.[/quote]I was thinking along the same lines as well, but since the default behavior is to not create an index, and no index was created, and there could be many duplicates with that identity - i decided that the SQL would not query it efficiently.Tue, 20 Apr 2010 10:05:08 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]JT-361866 (4/20/2010)[/b][hr]The first part of the question was fine, but the second "SQL Server does not have an efficient way to retrieve rows based on a known value for PersonID." is not only poorly worded but the answer is just plain wrong.SQL Server DOES IN FACT HAVE an efficient way to retrieve rows based on a known value for PersonID.[/quote]Not with the table as given in the question. All of the ways you list involve modifying the table itself, which is outside of the scope of the question.Tue, 20 Apr 2010 09:23:30 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote][b]sknox (4/20/2010)[/b][hr][quote][b]Hugo Kornelis (4/20/2010)[/b][hr][quote][b]Tom Brown (4/20/2010)[/b][hr]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.[/quote]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![/quote]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.[/quote]Man, oh man. I have been told my soooo many experienced DBAs and trainers that an IDENTITY will NOT be duplicated under any conditions. This is a myth that gets promulgated by trainers and those who should know better. And, it's a very dangerous one. Good thing I put a unique constraint when I use IDENTITY. Thanks for this Hugo. I just got a brainstorm (and boy, does it hurt) that someone should write an article about SQL Server myths.Tue, 20 Apr 2010 09:13:46 GMTOCTomhttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspxThe first part of the question was fine, but the second "SQL Server does not have an efficient way to retrieve rows based on a known value for PersonID." is not only poorly worded but the answer is just plain wrong.SQL Server DOES IN FACT HAVE an efficient way to retrieve rows based on a known value for PersonID.Here are three ways to increase the efficiency of retrieving rows based on known values for the PersonID column1. Adding a Primary Key to that column2. Adding a Unique Index or Unique Constraint to that column3. Adding a general Index based on the PersonID columnThe question should have read something like this:"By default SQL Server does not have an efficient way to retrieve rows based on a known value for PersonID."Tue, 20 Apr 2010 09:09:05 GMTJared Turnerhttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspx[quote]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 [u]really[/u] 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![/quote]I did just a couple post ago. :-PTue, 20 Apr 2010 08:39:29 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspxHi, 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 [u]really[/u] 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 [i]Michael[/i]Tue, 20 Apr 2010 08:36:30 GMTMichael Poppershttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspxShould 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 :-)Tue, 20 Apr 2010 08:33:01 GMTrjv_rnjnhttp://www.sqlservercentral.com/Forums/Topic906457-1328-1.aspxExcellent 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.Tue, 20 Apr 2010 08:13:25 GMTSean Lange