Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am not sure as to properly use non-clustered indexes. The SSMS plan estimator said to add another index when the following index was on the table already.

    CREATE NONCLUSTERED INDEX [ix_zone_fetch_shipping] ON [dbo].[tbl_shipping_rates_zones]
        [iso] ASC, [mzone] ASC, [postal] ASC
        [region], [zone_dom], [zone_emi], [zone_pmi], [zone_fci],
        [zone_ups], [zone_fed]

My query was

SELECT * FROM tbl_shipping_rates_zones WHERE postal = '10001'

My question is... should I be creating an index for all possible look up keys? I search by mzone, iso, and postal in different queries.


share|improve this question
Are you including every column from the table? – Mr.Brownstone Sep 5 '12 at 19:04
Yes I am including every column. – Alex Erwin Sep 5 '12 at 19:20
Including all columns in a single index is not going to help much - such an index first of all basically doubles the space requirements, and secondly, it can only ever be used for the n left-most columns - it does NOT just magically make searching for the 17th column in your table quicker. – marc_s Sep 6 '12 at 0:33
And having a separate index for each column - just because you might search for it some day - is also not very good. An index does cost you - in terms of peformance on every insert, update, delete. Don't overindex - that's almost worse than having no indexes at all.... – marc_s Sep 6 '12 at 0:34
Thank you for those comments @marc_s, that is exactly the info I was looking for. Also, is it appropriate to say there is a good range of non-clustered indexes to have (between 5-10 for example) or is it purely relative to the needs of the table? If so, what kind of things is it relative to? – Jeff.Clark May 10 at 23:18
up vote 6 down vote accepted

SQL Server will show you an Index it would like to use, since it estimates that index would make life easier. There is certainly no need to create an index on every searchable field, in fact doing so will make write performance substantially worse. If you have less than say 5 to 10 indexes on the given table, and you run that query all the time, you probably want to add the suggested index exactly as SSMS shows it.

JNK's Edit Below

It's also important to look at the suggested indexes together. The suggestions are all in silos, so to speak, so one entire suggested index could be replaced by adding an INCULDEd field on another index.

For instance, it's possible to have suggestions for these two indexes:

CREATE INDEX ix_IndexA ON Table (ColA, ColB)


When in fact you could meet the needs of both with:

CREATE INDEX ix_IndexC ON Table (ColA, ColB) INCLUDE (ColC)

I've seen this with much more complicated indexes (10-20 key fields, 10-20 included fields) where adding one field to the INCLUDE list will eliminate the need for the second index.

share|improve this answer
Would the ideal scenario be to no create any indexes and then run the statements and see what SSMS suggests? – Alex Erwin Sep 5 '12 at 19:23
Thanks for your help! – Alex Erwin Sep 5 '12 at 19:29
It's also important to look at the suggested indexes together. The suggestions are all in silos, so to speak, so one entire suggested index could be replaced by adding an INCULDEd field on another index. – JNK Sep 5 '12 at 19:32
@MaxVernon No blog post but I'll add an example to your answer that you can feel free to delete :) – JNK Sep 5 '12 at 20:03
@MaxVernon: read Stairway to SQL Server Indexing - Part 1, Introduction to Indexes and then the rest of the articles in the series - covers pretty much everything about SQL Server indexes, from intro to advanced topics – marc_s Sep 6 '12 at 1:31

The reason it is telling you to create a new index is because the one you have is not the most optimal one possible. The query optimizer is quite selective in the way that it chooses which index to use, the following link should help you understand selectivity:

You said above that you have included all columns within the index - essentially duplicating the table data. On this basis, what would be the harm in creating 3 separate indexes on the columns you mention? The space used would be less and the speed would be much faster. I would hazard a guess that this is what the DTA/execution plan is telling you - just in a different way.

Look at the execution plan details that are generated by the query to identify where your bottle necks are and use that as a guideline as to how to design your indexes. SQL Server is pretty good at knowing what is best - trust in what it is telling you.

I hope this helps you.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.