Post reply

Creating indexes with same key column but different include

  • July 1, 2015 at 6:51 am

    #147512

    I wanted to know if it would be feasible to create two indexes with the same key column but using includes with different columns. I know this is a judgment call but has anyone experienced good/bad results with this. Below is a sample of what I would like to do:

    CREATE NONCLUSTERED INDEX IXNC__T1__ColKey__Cover2 ON [dbo].[T1] ([ColKey])

    Include (ColB,ColC)

    CREATE NONCLUSTERED INDEX IXNC__T1__ColKey__Cover4 ON [dbo].[T1] ([ColKey])

    Include (ColD,ColE,ColF,ColG)

    Thanks

  • July 1, 2015 at 7:29 am

    #1809470

    Feasible, but probably not a good idea.

    Unless the query which uses the smaller one is so time-sensitive that it cannot possibly take a millisecond longer, you're probably best off merging those two into one 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

  • July 1, 2015 at 8:45 am

    #1809522

    I'd defer to Gail here, and I agree. Merge the indexes. AFAIK, the order of columns in the include doesn't matter.

  • July 1, 2015 at 8:49 am

    #1809525

    Steve Jones - SSC Editor (7/1/2015)

    AFAIK, the order of columns in the include doesn't matter.

    Correct.

    Order of columns matters in the key, not in the include

    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 4 posts - 1 through 3 (of 3 total)

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