Index Key Order

Question

Post reply

Index Key Order

BSavoie

SSCertifiable

Points: 6309
More actions
April 11, 2012 at 3:53 pm

#253898

Is the order of the key components critical in an index? In other words given the table/indexes below, is there a significant difference between the two indexes? Most of the searches will be by SubscriberId, MemberId, GroupId. Does the index key have to be in that same order?
create table #Insured (
SubscriberId varchar(13)
,MemberId varchar(13)
,GroupId varchar(10)
,LName varchar(25)
,FName varchar(15)
)
CREATE NONCLUSTERED INDEX [IX_Order1] ON #Insured
(
[GroupID] ASC,
[SubscriberID] ASC,
[MemberID] ASC
)
CREATE NONCLUSTERED INDEX [IX_Order2] ON #Insured
(
[SubscriberID] ASC,
[MemberID] ASC,
[GroupID] ASC
)
Thanks
.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Yes, order of index keys is important.

http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

CapnHector SSCoach Points: 16143 More actions · Answer 2

Gail's blog posts helped me out alot when i was trying to figure out my indexes on a table(Thanks Gail). read them and if you have any questions after you have reread them once more and you may answer your questions or it may raise some questions you had not thought of before.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Nadrek SSC-Insane Points: 20039 More actions · Answer 3

Read Gail's articles, and then test it to see for yourself how your particular data and queries behave.

Use the one index, then the other index - either with an index hint for benchmarking purposes only, or by having only one of the two created at a time.

BSavoie SSCertifiable Points: 6309 More actions · Answer 4

Ok, I'll check Gail out. She's had a couple good answers for me before. Should have looked there first. I've been going through the "Stairway to indexes" stuff, I'm on like 7. If my answer is in there, I missed it. Great stuff though.

.