Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In an effort to be more efficient when creating a new index, if there is already an index with the same index keys, but it simply needs additional include columns, is it better to create a second index (again, with the same keys) with different include columns? Or would it be more efficient to the query engine (not to recreate the index) to recreate the index and add the include columns to the original include column set?

If simply adding the include columns to the existing index, does it matter what order the include columns are appended?

Is there a scenario where it would make more sense to create a second index, duplicating the index keys, but with a different set of include columns?

I've attempted to simplify this question in code below.

    CREATE NONCLUSTERED INDEX [IX_Index_A] ON [dbo].[Table]
    ([Col1] ASC)
    INCLUDE ([Col2])

    CREATE NONCLUSTERED INDEX [IX_Index_B] ON [dbo].[Table]
    ([Col1] ASC)
    INCLUDE ([Col3],[Col4])

OR

    DROP INDEX [IX_Index_A] ON [dbo].[Table]

    CREATE NONCLUSTERED INDEX [IX_Index_A] ON [dbo].[Table]
    ([Col1] ASC)
    INCLUDE ([Col2],[Col3],[Col4])
share|improve this question

2 Answers 2

up vote 4 down vote accepted

The included field order doesn't matter since there's no sorting involved - those fields are available at the leaf level of the index for every row.

There's not really a disadvantage to consolidating them like in your second example, except for page density on that index (more page reads per number of rows since there's more data stored for each row).

There ARE measurable advantages, though - for updates/inserts on Col1 you only need to update one index instead of two, and you will save a lot of space by not keeping duplicate key entries for col1 in two separate indexes.

The only scenario I can think of that would make sense to have the two narrower indexes is if one of those was designed specifically for a mission critical procedure or view. If you have an index that includes only what you need for a certain query that gets run with high frequency it would make sense to keep it as streamlined as possible.

share|improve this answer

I would say consolidate your indexes in this case as for every insert, delete, and appropriate updates would require index maintenance * n indexes. Also, you can use the WITH (DROP_EXISTING = ON, ONLINE = ON) to reduce outage if you have Enterprise Edition.

CREATE NONCLUSTERED INDEX [IX_Index_A] ON [dbo].[Table]
([Col1] ASC)
INCLUDE ([Col2],[Col3],[Col4])
WITH(DROP_EXISTING = ON, ONLINE = ON)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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