CODEWITHSUNDEEP
sql-serverindexing
jlb
jlb

Reputation: 19970

Single index with multiple INCLUDE columns or multiple indices each with single INCLUDE

After doing some 'missing index' analysis via the DMV tables (and thanks to this guide), I discovered that I have a few index candidates on a single table.

The output query some something like this:

Total Cost  Table Name  equality_columns  inequality_columns  included_columns
1615619     [TableA]    [ColumnA]         NULL                [ColumnB]
1494475     [TableA]    [ColumnA]         NULL                [ColumnC]

(the *_columns columns are coming directly from sys.dm_db_missing_index_groups)

In this case, would it be OK to create a single index on ColumnA including both ColumnB and ColumnC, or would it be better two have separate indexes on ColumnA, one that includes ColumnB and one that includes ColumnC?

(Using SQLServer 2008 RC2)

Upvotes: 2

Views: 193

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28890

would it be OK to create a single index on ColumnA including both ColumnB and >ColumnC

I would go with this option

would it be better two have separate indexes on ColumnA, one that includes ColumnB and one that includes ColumnC?

creating index with this option is just wastage of space and unnecessary overhead as these columns are in included list and not in key column list

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269653

I think it is better to have one index: (ColumnA INCLUDE (ColumnB, ColumnC)).

Having two indexes with the same sorting keys in the same order is redundant. The additional included columns in the index can be used, regardless of the order in which they appear. Having a second index to include them just adds overhead because of the need to maintain two indexes.

Upvotes: 3

Related Questions