SA Developer .NET

Which CLUSTERED INDEX would be better:

1. CountryID, ProvinceID, StreetID

 or

2. StreetID, ProvinceID, CountryID

This is a 2 part question:

If I have a CLUSTERED INDEX consisting of CountryID, ProviceID, StreetID, do I need to create a NON CLUSTERED INDEX on ProvinceID if I have a large number of queries that are based on ProvinceID only.

Hi,

I'd say 1. because you are probably going to make calls in that order to the D.B.

NeverDie:

This is a 2 part question:

If I have a CLUSTERED INDEX consisting of CountryID, ProviceID, StreetID, do I need to create a NON CLUSTERED INDEX on ProvinceID if I have a large number of queries that are based on ProvinceID only.

I would think so because it is a seperate process (or D.B. call).

NeverDie:

Which CLUSTERED INDEX would be better:

1. CountryID, ProvinceID, StreetID

 or

2. StreetID, ProvinceID, CountryID

Depends on the queries that you have. The optimal order for columns in an index is determined by the queries you have on that table. Nothing else.

If all your queries are equality queries on all three columns, then they're both fine and the queries will execute the same with either one. If you have queries on CountryID alone, or CountryId and ProvinceID then the first one is more useful. If you have lots of queries on StreetID alone or StreetID and ProvinceID then the second one is more useful.

Oh, and the order in which you specify the conditions in the where clause is absolutely irrelivent. The optimiser can and will reorder conditions for to get an optimal execution plan

NeverDie:

This is a 2 part question:

If I have a CLUSTERED INDEX consisting of CountryID, ProviceID, StreetID, do I need to create a NON CLUSTERED INDEX on ProvinceID if I have a large number of queries that are based on ProvinceID only.

Yes you do, because indexes are only useful for queries that filter on a left-based subset of the index columns. ie, your index on CountryID, ProvinceID, StreetID can be used for an index seek if the queries filter (equality) on any of the following:
CountryID
CountryID and ProvinceID
CountryID, ProvinceID and StreetID