Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Non-clustered Covering Indexes - Many w/few included columns vs Single w/many included columns RE: Non-clustered Covering Indexes - Many w/few included columns vs Single w/many included columns

  • 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.