Post reply

Shoud you add clusteirng key in included col of non clustered index?

  • October 2, 2011 at 4:10 pm

    #390527

    I have a table named Contact, with a clustered index on the ContactID.

    I have two non clustered indexes:

    CREATE NONCLUSTERED INDEX IX_contact_firstName_1

    ON [dbo].[contact] ([firstname])

    INCLUDE ([middlename],[LastName])

    CREATE NONCLUSTERED INDEX [IX_contact_firstName_2]

    ON [dbo].[contact] ([FirstName])

    INCLUDE ([ContactID],[MiddleName],[LastName])

    The optimizer always picks the second index, the one with ContactID in the include.

    I thought that if you had a non clustered index, you got the clustered index key inclued by default.

    why then did the optimizer recommend i create the 2nd index ( that includes the clustering key ContactID)?

    Thanks!

  • October 2, 2011 at 5:32 pm

    #1389250

    Because the optimiser doesn't consider the definition of the clustered index when recommending non-clustered indexes.

    If you need the clustering key in an non-clustered index, specify it. If you don't, leave it out. Do not depend on SQL's behaviour. If you need the second index but create the first because you know that SQL will include the clustering key, what happens the day someone changes the clustered index?

    Don't design objects or write code in such a way that an unrelated change breaks your queries. That's brittle code and bad practice.

    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

  • October 3, 2011 at 12:42 pm

    #1389750

    The only reason to specifically include the clustering key in another index would be that's how you wanted to seek on the index. That means you are seeking on some column plus the clustering key. I've only seen this type of scenario a few times where it actually made sense and worked properly.

    There's another scenario where you have a very wide table with many columns and most of the time you only want a few fairly narrow columns and you seek on the clustering key. In this scenario you create a non-clustered index on the clustering key plus those few columns. This avoids seeking through the wide table (same as clustered index) for just a few columns. Again, I've only seen this work a few times where there was an actual improvement in performance.

    Todd Fifield

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

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