Post reply

Non-clustered Covering Indexes - Many w/few included columns vs Single w/many included columns

  • March 20, 2013 at 9:25 am

    #291397

    I have several non-clustered covering indexes on a table that have the same key columns with few included columns. I'm wondering if i can just create one NC covering index with all the included columns from each of the existing indexes.

    For example (K = key column, IC = included column),

    IDX 1 = K1,K2 + IC1,IC2

    IDX 2 = K1,K2 + IC3,IC4

    IDX 3 = K1,K2 + IC5,IC6

    IDX 4 = K1,K2 + IC4,IC6

    If I remove these and make one index (e.g., IDX = K1,K2 + IC1,IC2,IC3,IC4,IC5,IC5), will the queries be just as efficent? Or does it depend on the situation (i.e., query execution frequency)?

  • March 20, 2013 at 9:57 am

    #1598999

    sixthzenz (3/20/2013)

    I have several non-clustered covering indexes on a table that have the same key columns with few included columns. I'm wondering if i can just create one NC covering index with all the included columns from each of the existing indexes.

    For example (K = key column, IC = included column),

    IDX 1 = K1,K2 + IC1,IC2

    IDX 2 = K1,K2 + IC3,IC4

    IDX 3 = K1,K2 + IC5,IC6

    IDX 4 = K1,K2 + IC4,IC6

    If I remove these and make one index (e.g., IDX = K1,K2 + IC1,IC2,IC3,IC4,IC5,IC5), will the queries be just as efficent? Or does it depend on the situation (i.e., query execution frequency)?

    I would consolidate the indexes. This will reduce the space used as 1) you will not be duplicating the two key columns + the clustered index key and 2) the included columns won't be duplicated either as it appears a couple of them are.

  • March 20, 2013 at 11:30 am

    #1599032

    Thanks, that was my initial instinct/thought but just wanted someone to confirm it. My reasoning, which I just learned, was based on the fact that the order of the included columns don't matter. If that's the case, it'd only make sense to have them all combined.

  • March 20, 2013 at 12:16 pm

    #1599062

    Yup, consolidate, that should be one index.

    The only counter case for keeping it separate would be where queries that use those indexes absolutely must run as fast as possible and not a microsecond more, in that limited case you would probably want multiple indexes for the fastest access possible (larger indexes = more data).

    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