index once with 2 keys or have 2 indexes with each key ? Confused

Question

index once with 2 keys or have 2 indexes with each key ? Confused

John Burchel

Hall of Fame

Points: 3611
More actions
February 14, 2007 at 10:35 am

#172035

Hi

I was wondering if whether having an index key as part of the only ky on an index or having It separately makes a difference.
i.e Create index index_test on Company (Company_ID)
Create index index_test on Company (Location_ID)

Or
Create index index_test on Company(Company_id,Location_ID)

If it doesn’t make a difference, does it make a difference as to which key comes first or is this irrelevant?
Kindest Regards,
John Burchel (Trainee Developer)

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

It depends. Not what you wanted to here is it? How you index a table depends on how it will be queried. If all queries are either by Company_ID only or by Company_I then Location_ID, you would want to create a single index with Company_ID first.

Indexing, however, isn't a science, its an art. One indexing scheme my improve some queries/inserts/updates/deletes, but could hamper others. You have to look at the entire system and make adjustments to benefit the whole system.

I am sure if you do a search on indexing on this site and also on http://www.sql-server-performance.com, you will find a variety of view points and guidelines for tuning a database.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

If you choose for the 2-column solution put the columns first which has the most unique values ! So it filters on top of the tree

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

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

As Lynn said, if depends on how the table is queried. If most of your queries filter on CompanyID alone, or LocationID alone, then you will want two indexes. If the most common queies are on CompanyID and LocationID then you want one index.

When you consider which column first, consider the queries. An index on CompanyID, LocationID can be used for a seek by a query filtering on CompanyID, but not for a query filtering on LocationID.

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

John Burchel Hall of Fame Points: 3611 More actions · Answer 4

Hi Guys,

Thanks for the info, I will have a word with stakeholders and formulate my index strategy.

thanks,

Kindest Regards,

John Burchel (Trainee Developer)

John Burchel Hall of Fame Points: 3611 More actions · Answer 5

One more question that I have, if you have the indexes seperate, does this take up more space ?, compared to having 2 keys in one index.

Thanks