Merging SQL Server indexes on same key but different INCLUDE columns

Question

Using Brent Ozar's sp_BlitzIndex to highlight missing indexes (Indexaphobia: High value missing index) from several SQL Server databases with a common layout but different end-users.

Some of the suggestions involve indexes with INCLUDE(...) and I'm not sure if I can combine several of these potential indexes - as an example say I have several sets of people (tblPeople) being queried in slightly different ways which has produced the following suggestions;

CREATE INDEX [ix_tblPeople_ZipCode] ON [dbo].[tblPeople] ([ZipCode]);

CREATE INDEX [ix_tblPeople_ZipCode_include_A] ON [dbo].[tblPeople] ([ZipCode]) INCLUDE ([FirstName]);

CREATE INDEX [ix_tblPeople_ZipCode_include_B] ON [dbo].[tblPeople] ([ZipCode]) INCLUDE ([LastName]);

CREATE INDEX [ix_tblPeople_ZipCode_include_C] ON [dbo].[tblPeople] ([ZipCode]) INCLUDE ([BusinessName]);

Would combining these into a single index which INCLUDE'd all those columns be useful for all three scenarios or is there a better approach?

CREATE INDEX [ix_tblPeople_ZipCode_include] ON [dbo].[tblPeople] ([ZipCode]) INCLUDE ([BusinessName], [LastName], [FirstName]);

score 0 · Accepted Answer · answered Dec 11 '15 at 10:31

Yes, that would probably be a good idea - fewer indexes means fewer maintenance overhead when you insert, update or delete rows.

And since those columns are merely included at the leaf-level, there's no negative impact on the navigation structure of the index, either.

score 0 · Answer 2 · edited May 23 '17 at 11:44

0

It depends.

If your queries have a where clause like

where  firstname  like 'John'
and    lastname   like 'Smith'
and    businesName like 'StackOverflow'
and    zipCode    like '12345'

Then the included columns will not be used - only the indexed column (zipCode) will be used to filter results.

Merging SQL Server indexes on same key but different INCLUDE columns

2 Answers2