Post reply

Indexes - Clustered indexed column included in a nonclustered index

  • July 27, 2014 at 10:01 am

    #291508

    Hi there - I have recently noticed that there are a lot of indexes in my database which include multiple fields including the primary key clustered index.

    I understand the benefits of having covering indexes however would there be performance gains if your nonclustered index also included the primary key (which already has a clustered index)?

    Thanks.

    --------------------------------------------

    Laughing in the face of contention...

  • July 27, 2014 at 10:12 am

    #1732668

    My general rule for this:

    If the clustered index key is needed in the index (to cover a query or to make it useful), then it should be there. If it isn't needed, don't specify it.

    SQL implicitly adds the clustered index key when needed, there's no use specifying it when you don't need it.

    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

  • July 27, 2014 at 10:18 am

    #1732670

    Thanks a lot!

    I thought this would be the case but wasn't entirely sure if you should not include a clustered index column in a nonclustered covering index. Thanks

    --------------------------------------------

    Laughing in the face of contention...

  • July 28, 2014 at 11:51 am

    #1732956

    All clustered keys must appear in all nonclustered indexes. If you don't explicitly specify them, SQL will add them automatically.

    I explicitly specify them when the index function requires them, otherwise not. That way, if the clustered index changes, the index definitions don't have to.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 4 posts - 1 through 3 (of 3 total)

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