Single sql column defined as 2 separate indexes-unique and non unique?

  • Juanita

    SSCertifiable

    Points: 5300

    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

  • Gail Shaw

    SSC Guru

    Points: 1004484

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Juanita

    SSCertifiable

    Points: 5300

    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

  • John Mitchell-245523

    SSC Guru

    Points: 148769

    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

  • Juanita

    SSCertifiable

    Points: 5300

    HI John,

    Yes, the both indexes are on the same column. Probably the best thing to do, let vendor fix it.

    Thank you!

  • Gail Shaw

    SSC Guru

    Points: 1004484

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Juanita

    SSCertifiable

    Points: 5300

    Thank you both so much !

  • John Mitchell-245523

    SSC Guru

    Points: 148769

    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

  • Juanita

    SSCertifiable

    Points: 5300

    John,

    Good point ! That would be my luck!

  • Gail Shaw

    SSC Guru

    Points: 1004484

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    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

  • John Mitchell-245523

    SSC Guru

    Points: 148769

    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

  • Gail Shaw

    SSC Guru

    Points: 1004484

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • John Mitchell-245523

    SSC Guru

    Points: 148769

    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

  • Gail Shaw

    SSC Guru

    Points: 1004484

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply