August 21, 2012 at 4:19 pm
HI, I have a database that was set up by a vendor application. I have discovered at least 10 occurrences where they defined 2 separate indexes on a single table referencing the exact same column. I understand that that would be a duplicate index. What i don't understand and have never seen before is that index1 on column1 is defined as unique - nonclustered and index2 on column1 is defined as non-unique and nonclustered. So is there a methodology here that I'm not understanding by defining the same column as unique and also an non-unique?
Any explanation would be greatly appreciated.
Juanita
August 21, 2012 at 5:10 pm
No good reason to do that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2012 at 9:09 am
Gail, Thank you so much for the reply. Would you have a recommendation on which index to delete? i am thinking i would keep the 'UNIQUE' index.
Thanks again !
Juanita
August 22, 2012 at 9:17 am
Juanita
Can I just clarify - are both indexes on the same single column?
To answer your question, I think you need to assume that one of the indexes was created unique for a reason and therefore you should remove the other. Maybe the vendor can confirm that?
John
August 22, 2012 at 9:20 am
HI John,
Yes, the both indexes are on the same column. Probably the best thing to do, let vendor fix it.
Thank you!
August 22, 2012 at 9:21 am
I would tend to agree with you.
The thing is, they're not quite the same indexes. The unique index (for reasons of index architecture) is essentially an index on the key column, include the clustered index key. The non-unique index is essentially an index on the key columns and the clustered index key.
That said, single column indexes aren't generally that useful anyway.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2012 at 9:37 am
Thank you both so much !
August 22, 2012 at 9:43 am
GilaMonster (8/22/2012)
The thing is, they're not quite the same indexes. The unique index (for reasons of index architecture) is essentially an index on the key column, include the clustered index key. The non-unique index is essentially an index on the key columns and the clustered index key.
My head hurts! That must mean that a unique index takes up more space since it has the clustering key as an included column(s)? I'll do some testing on that as soon as I get a chance, to help me understand it.
Juanita, definitely get the vendor to advise you if you have that option. One thing that can burn you is index hints. I once removed a duplicate index from a vendor's database, only for some code to fail because it included a hint for the index that I'd removed!
John
August 22, 2012 at 9:48 am
John,
Good point ! That would be my luck!
August 22, 2012 at 10:51 am
John Mitchell-245523 (8/22/2012)
GilaMonster (8/22/2012)
The thing is, they're not quite the same indexes. The unique index (for reasons of index architecture) is essentially an index on the key column, include the clustered index key. The non-unique index is essentially an index on the key columns and the clustered index key.My head hurts! That must mean that a unique index takes up more space since it has the clustering key as an included column(s)? I'll do some testing on that as soon as I get a chance, to help me understand it.
No. Less actually (very slightly less). See highlighted phrase that you may have missed.
Unique: Clustered index key as include column
Not unique: Clustered index key as part of index key columns.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2012 at 10:26 pm
GilaMonster (8/22/2012)
John Mitchell-245523 (8/22/2012)
GilaMonster (8/22/2012)
The thing is, they're not quite the same indexes. The unique index (for reasons of index architecture) is essentially an index on the key column, include the clustered index key. The non-unique index is essentially an index on the key columns and the clustered index key.My head hurts! That must mean that a unique index takes up more space since it has the clustering key as an included column(s)? I'll do some testing on that as soon as I get a chance, to help me understand it.
No. Less actually (very slightly less). See highlighted phrase that you may have missed.
Unique: Clustered index key as include column
Not unique: Clustered index key as part of index key columns.
Is this to say that in a not unique index that SQL Server will attempt to insert new index entries with an existing index key in the order of the clustered index?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 23, 2012 at 1:46 am
opc.three (8/22/2012)
Is this to say that in a not unique index that SQL Server will attempt to insert new index entries with an existing index key in the order of the clustered index?
I think the entries would be inserted in the (logical) order of the non-clustered key, with the clustering key acting as a tie-breaker.
GilaMonster (8/22/2012)
John Mitchell-245523 (8/22/2012)
GilaMonster (8/22/2012)
The thing is, they're not quite the same indexes. The unique index (for reasons of index architecture) is essentially an index on the key column, include the clustered index key. The non-unique index is essentially an index on the key columns and the clustered index key.My head hurts! That must mean that a unique index takes up more space since it has the clustering key as an included column(s)? I'll do some testing on that as soon as I get a chance, to help me understand it.
No. Less actually (very slightly less). See highlighted phrase that you may have missed.
Unique: Clustered index key as include column
Not unique: Clustered index key as part of index key columns.
Gail, please will you clarify this? The Table and Index Organization topic in Books Online says: "Each index row contains the nonclustered key value, a row locator and any included, or nonkey, columns". Do I take it that in a unique index, the row locator and the the included column(s) are one and the same thing?
Thanks
John
August 23, 2012 at 2:14 am
No. The include columns are what you specify in the INCLUDE section of the index definition
"Each index row contains the nonclustered key value, a row locator and any included, or nonkey, columns"
The row locator is the RID (if the base table is a heap) or the clustered index key (if the base table is a clustered index). BoL doesn't say anything about where in the index those go, and that's all I've been trying to explain.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2012 at 2:30 am
GilaMonster (8/23/2012)
No. The include columns are what you specify in the INCLUDE section of the index definition"Each index row contains the nonclustered key value, a row locator and any included, or nonkey, columns"
The row locator is the RID (if the base table is a heap) or the clustered index key (if the base table is a clustered index). BoL doesn't say anything about where in the index those go, and that's all I've been trying to explain.
Still confused. If the unique index definition doesn't have an INCLUDE section, is the clustered index key still an include column? And if it is, but it isn't one and the same with the row locator, wouldn't that make the unique index bigger than the non-unique index?
John
August 23, 2012 at 2:48 am
Yes. As Bol says, the key, the row locator and then any include columns. No include columns, that last section is empty then.
No. The clustered index key IS the row locator for a table that has a clustered index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply