SQL Overlapping and Multi-Column Indexes
Posted
by durilai
on Stack Overflow
See other posts from Stack Overflow
or by durilai
Published on 2010-03-08T23:44:25Z
Indexed on
2010/03/09
1:06 UTC
Read the original article
Hit count: 341
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]
© Stack Overflow or respective owner