What fields to add in an index

Question

What fields to add in an index

SpeedSkaterFan

SSCrazy

Points: 2716
More actions
November 20, 2014 at 11:42 am

#312727

Hi all,
I have read quite a bit about adding an index to a table, and understand the importance. I think I get the general idea, but I am confused what field to include.
E.g.: a table with the fields:
ID (Primary key)
FirstName
Lastname
Gender
Nationality
Remarks
I guess I have to create a clustered index on ID.
Apart from that, I would like to add all fields, except "Remarks" to an index
* Should I create a seperate index for all remaining 4 fields
* Or should I create a combined index? e.g.:
> ID, Lastname, FirstName, Gender, Nationality
> ID, Gender, LastName, FirstName, Nationality
> ID, Nationality, Gender, LastName, FirstName
Thanks in advance
Hein

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

SpeedSkaterFan (11/20/2014)
Hi all,
I have read quite a bit about adding an index to a table, and understand the importance. I think I get the general idea, but I am confused what field to include.
E.g.: a table with the fields:
ID (Primary key)
FirstName
Lastname
Gender
Nationality
Remarks
I guess I have to create a clustered index on ID.
Apart from that, I would like to add all fields, except "Remarks" to an index
* Should I create a seperate index for all remaining 4 fields
* Or should I create a combined index? e.g.:
> ID, Lastname, FirstName, Gender, Nationality
> ID, Gender, LastName, FirstName, Nationality
> ID, Nationality, Gender, LastName, FirstName
Thanks in advance
Hein

Just based on table structure alone there is no right answer here. It would all depend on how you are going to be querying this table. Just putting an index on a column because you think you should is almost certainly going to result in worse performance. You need to understand indexing and why you would use or need an index. And no, you do NOT have to create a clustered index on ID. Just because a column is the primary key does not mean it must also be the clustered index.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SpeedSkaterFan SSCrazy Points: 2716 More actions · Answer 2

Hi Sean

Thx for your reply

The fields I did mention in my question are the fields that will be used most in queries.

Sometimes all fields will be necessary, sometimes only (eg) FirstName, LastName and Gender.

I have searched for examples on the site and further but haven't found any answer and or example that gives me the confidence I am looking for.

Again, the fields mentioned used used in a lot of queries, in different order and they are not always the same

Is this more help?

Hein

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

SpeedSkaterFan (11/20/2014)
Hi Sean
Thx for your reply
The fields I did mention in my question are the fields that will be used most in queries.
Sometimes all fields will be necessary, sometimes only (eg) FirstName, LastName and Gender.
I have searched for examples on the site and further but haven't found any answer and or example that gives me the confidence I am looking for.
Again, the fields mentioned used used in a lot of queries, in different order and they are not always the same
Is this more help?
Hein

Well you can easily exclude gender as needing an index. There really should only be 2 (or 3 if it allows NULL) values no matter how large the table gets so an index isn't going to help here.

So your queries are things like "Where FirstName = 'something' and LastName = 'something' and Gender = 'M' and Nationality = 'SomeCountry'? Will the order of columns be the same? More importantly will you always check both of them or can you maybe look for only 1 or the other?

There is almost an equal portion of science and art in setting up the correct index strategy. Check out this link. Gail does an awesome job of describing the process, how to determine what is best and gives lots of excellent external references on the topic as well.

http://sqlinthewild.co.za/index.php/2011/11/11/sql-university-advanced-indexing-indexing-strategies/[/url]