SQL Overlapping and Multi-Column Indexes
- by durilai
I am attempting to tune some stored procedures and have a question on indexes. I have used the tuning advisor and they recommended two indexes, both for the same table. The issue is one index is for one column and the other is for multiple columns, of which it includes the same column from the first. My question is why and what is the difference?
CREATE NONCLUSTERED INDEX [_dta_index_Table1_5_2079723603__K23_K17_K13_K12_K2_K10_K22_K14_K19_K20_K9_K11_5_6_7_15_18]
ON [dbo].[Table1] (
[EfctvEndDate] ASC,
[StuLangCodeKey] ASC,
[StuBirCntryCodeKey] ASC,
[StuBirStOrProvncCodeKey] ASC,
[StuKey] ASC,
[GndrCodeKey] ASC,
[EfctvStartDate] ASC,
[StuHspncEnctyIndctr] ASC,
[StuEnctyMsngIndctr] ASC,
[StuRaceMsngIndctr] ASC,
[StuBirDate] ASC,
[StuBirCityName] ASC
) INCLUDE (
[StuFstNameLgl],
[StuLastOrSrnmLgl],
[StuMdlNameLgl],
[StuIneligSnorImgrntIndctr],
[StuExpctdGrdtngClYear]
) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
ON [PRIMARY] go
CREATE NONCLUSTERED INDEX [_dta_index_Table1_5_2079723603__K23]
ON [dbo].[Table1] (
[EfctvEndDate] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
ON [PRIMARY]
