SQL Server Index Tuning – Best Practices for Inversed Key/Include Columns

index-tuningsql server

I have an index on a table of the form:

NONCLUSTERED INDEX [IDX1] ON [TABLE]
(
    [COL_ID_A] ASC
)
INCLUDE
(
    [COL_ID_B]
)

For a different query on the same table MSSMS proposes the following index where the key and include columns are reversed:

NONCLUSTERED INDEX [IDX2] ON [TABLE]
(
    [COL_ID_B] ASC
)
INCLUDE
(
    [COL_ID_A]
)

What is the best practice (and why)?:

  1. one index with keys and indexes combined or
  2. two separate indexes

Best Answer

You may read more about non-clustered index here from Brent Ozar and Team. Thumb rule for column in the index should be used in where clause and include is the column which is in the select statement.

Since, you have not posted query here, you may understand with below examples:

/*Query1
********/
select [COL_ID_B]
from [TABLE]
where [COL_ID_A] = some value

Above query Query1 will use index IDX1, whereas Query2 will use IDX2.

/*Query2
*********/
select [COL_ID_A]
from [TABLE]
where [COL_ID_B] = some value

Now, there is difference between index column and include column, you can read more about this on article written by Mr. Ahmad Yaseen. There is also concept of hidden column in case you have clustered index on this table, clustered column will be part of all non-clustered index, you may refer this article.

Whether one index should contain multiple column or multiple index should be created with one column, it depends on application and query pattern. Thumb rule is to have five non-clustered index on 5 columns however this varies from application to application.

First column in index is extremely important, if your query selects col1 and looks mostly for colA followed by colB, colC and colD respectively then, you should go for a non-clustered index with below definition:

CREATE NONCLUSTERED INDEX [IDX3] ON [TABLE]
(
    colA,
    colB,
    colC,
    colD
)
INCLUDE
(
    col1
)

however if your queries have where clause on all columns and heavily uses all of them i.e. colA, colB, colC and colD then, five separate non-clustered index on each of them would help. Again column in select could be chosen as include.

There are tonnes of article on each of these topic by many experts. you may refer below for more details:

https://www.brentozar.com/archive/2018/11/tales-from-overindexing-too-many-one-column-indexes/

https://www.brentozar.com/archive/2015/04/index-included-columns-multi-column-statistics/

Similar question was asked and you may find answers here

Please let us know if this helped.

Related Question