Post reply

Any valid reason to use a unique column in composite index?

  • September 28, 2011 at 12:58 am

    #243789

    Does anybody know a good reason why we would build a composite index using RID where RID is a PK?

    index = [RID,Col2,Col3]

    My not so Common sense dictates that if RID is a PK (unique), then by querying on RID, you will get a unique record, and no further condition should be needed to refine the results. So then a composite should only contain non unique columns.

    But maybe there is a valid reason I am not aware of since a DBA I am not.

    I cannot find any info regarding this issue, but all the examples always have non unique columns in the composite key.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • September 28, 2011 at 1:35 am

    #1387579

    Like that there's seldom a reason, especially if you're always doing equality matches on the column. If the columns were in another order, there could be a use.

    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

  • September 28, 2011 at 2:36 am

    #1387597

    Thanks Gail.

    Would you even consider this as a way to do avoid a lookup to the table for col2,and 3?

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • September 28, 2011 at 2:43 am

    #1387599

    Not unless the primary key is enforced by a nonclustered 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

  • September 28, 2011 at 2:45 am

    #1387600

    Thanks Gail

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • September 28, 2011 at 6:11 am

    #1387692

    I have used a unique value in a compound index before, but not one that was already indexed, let alone the PK on the table and, I assume, the clustered index.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • September 29, 2011 at 1:33 am

    #1388206

    I usually try to avoid non-unique indexes, adding the primary key after the key fields; in this case, an index (Col2, Col3, RID) has sense for me. It is a common practice?

    Francesc

  • September 29, 2011 at 2:14 am

    #1388215

    Hi,

    Composite Index is used when you want to have only the Unique Records in a combination of more than one column..

    For Instance, in Email DB, you want to have unique email id from a particular Competition webpage then Create Composite Key on SourceID(refering to Particular Competion) and Email ID. This is will capture the email id once from a particular competion page.

    Regards

    Palash Gorai

  • September 29, 2011 at 2:25 am

    #1388219

    frfernan (9/29/2011)

    I usually try to avoid non-unique indexes, adding the primary key after the key fields; in this case, an index (Col2, Col3, RID) has sense for me. It is a common practice?

    Francesc

    I think it is common practice if your table is a heap since if you exclude RID from the index, the query will need a lookup from the index to the table to retrieve the rid if it was not included.

    But then the first question is, why is the table a heap?

    But if the table is clustered on RID, my guess is that the RID is implicitly included in the non clustered index as part of a pointer to the table record so it can satisfy a query which outputs an RID with the index by itself without io to the table.

    So an index on [col2]when table clustered on RID should work better than an index with [col2,RID] since the index is smaller but should work the same.

    Of course of this I am not 100% so hopefully we can have more participation. So I'll start a new topic.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • September 29, 2011 at 3:05 am

    #1388229

    One possible use of an index on (RID, Col2, Col3) would be as a covering index for some query - if your table's columns are RID, Col2, Col3, BigVarcharCol4, BigVarcharCol5, BigVarcharCol6, ..., and all you need is Col2 and Col3 for a range of RIDs, a scan on the smaller index could be more efficient than the corresponding clustered index scan.

  • September 29, 2011 at 3:10 am

    #1388236

    frfernan (9/29/2011)

    I usually try to avoid non-unique indexes, adding the primary key after the key fields; in this case, an index (Col2, Col3, RID) has sense for me. It is a common practice?

    No. A column should be added to an index only if it's needed in that index. If it's not needed, it should not be specified. Conversely, if a column is needed it should be specified and SQL's behaviour regarding the clustered key not replied upon.

    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 11 posts - 1 through 10 (of 10 total)

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