Should I create a nonclustered index on all searchable columns or just each column separately?

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
    )
    INCLUDE
    (
        [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.

Thanks

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)

CREATE INDEX ix_IndexB ON Table (ColA) INCLUDE (ColC)

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.

StackExchange Database Administrators Q#23742, answer score: 6