1

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
  • 1
    Could you post the queries please? – George.Palacios Apr 8 at 9:27
  • 1
    The first index will be most useful to cover a query with COL_ID_A in a predicate and COL_ID_B used elsewhere. The second will cover a query with COL_ID_B in a predicate and COL_ID_A used elsewhere. Since only the leftmost key column(s) can be used for seeks, 2 separate indexes are needed. Include the DDL and queries for a more specific answer. – Dan Guzman Apr 8 at 9:56
2

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.

  • I'm not sure this answers the OP's question - you haven't answered whether they should use one or two separate indexes – George.Palacios Apr 8 at 10:07
  • @George.Palacios I will edit my answer and include those points also. Thank you for pointing out. – Learning_DBAdmin Apr 8 at 10:10
  • 1
    Accepted. Very useful post that guided me to the anwser (2 distinct indexes) while clarifying my muddy understanding of basic best practices – Tom Carter Apr 9 at 14:10
-3

you should try to create only one index. creating seprate index would consume more IO and create overhead for DML operations. NONCLUSTERED INDEX [IDX1] ON [TABLE] ( [COL_ID_A],[COL_ID_B] ) this index would cover both columns and suitable for both types of queries coming for col a and col b as predicates.

Scneraio: 1. Query one will search values inside the index and get correspondinf values of column b on same leaf level page. Same will be applied when query two search for Column b and find column a in select list.

Note :- statistics for both columns should be updated to get optimal execution plan.

  • 2
    Key column order matters. You can't seek to COL_ID_B values unless you also have an equality predicate on COL_ID_A, with your example. – Erik Darling Apr 8 at 12:13
  • @Vinod not really. Query with WHERE col_b = @X won't be able to use your composite index as effectively as it could with an index on (col_b). – ypercubeᵀᴹ Apr 8 at 12:13
  • Cardinality estimates are created default for first column.But index structure created for both columns. if i create two indexes as stated by you consume 20 mb space and same area acquires in buffer pool. Another composite index consume only 10 mb index and less overhead on dml operations as well. Statistics play important role if exist for column b as well. – Vinod Narwal Apr 8 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.