Index column order?

I need to create an index with multple columns, for example, an index
contains "State", "City" and "Street". Obviously the density of the columns
is State < City < Street. Is it necessary to follow a particular order when
create index for better performance? For example (State, City, Street) or
(Street, City, State)?Yes, it can have a huge impact. Higher selectivity = less rows traversed =
better performance. Also remember that index statistics are only computed
for the first column in the index; statistics play a large part when the
optimizer does its job, so it's important to keep that in mind.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"nick" <nick@.discussions.microsoft.com> wrote in message
news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
> I need to create an index with multple columns, for example, an index
> contains "State", "City" and "Street". Obviously the density of the
columns
> is State < City < Street. Is it necessary to follow a particular order
when
> create index for better performance? For example (State, City, Street) or
> (Street, City, State)?|||Hi
Based on your where clause.
If you have an index on State, City, Street and use a where clause City,
State, Street, the index will not be used. In this case, SQL can not guess
that state is of any use, and it then can't use the index for the sort.
Maybe and Index on state, a separate one on city, and a separate one on
street.
A compound index is generally a bad idea as a data modification will result
in a lot of index maintenance, slowing down the operation.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"nick" <nick@.discussions.microsoft.com> wrote in message
news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
>I need to create an index with multple columns, for example, an index
> contains "State", "City" and "Street". Obviously the density of the
> columns
> is State < City < Street. Is it necessary to follow a particular order
> when
> create index for better performance? For example (State, City, Street) or
> (Street, City, State)?|||If you frequently search on some subset of the indexed columns then put
those columns first. Otherwise, put the most selective first (the column
with the highest cardinality). So I expect that's (Street, City, State).
David Portas
SQL Server MVP
--|||"nick" <nick@.discussions.microsoft.com> wrote in message
news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
> I need to create an index with multple columns, for example, an index
> contains "State", "City" and "Street". Obviously the density of the
columns
> is State < City < Street. Is it necessary to follow a particular order
when
> create index for better performance? For example (State, City, Street) or
> (Street, City, State)?
That depends on what you are searching for, and how you want your data
sorted.
If you were selecting city and street, then the State, City, Street index
would not be useful.
Think of the data in your index as if it were listed in that order in your
phone book. If you want to find everyone in the state, sorted by city and
street, then having the phone laid out by State, City and then Street would
work best for that particular query.
Take a look at the index tuning wizard. It can provide you a lot of great
information about which indexes to create given a workload.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Tips on Optimizing SQL Server Composite Indexes
http://www.sql-server-performance.c...ite_indexes.asp
AMB
"nick" wrote:

> I need to create an index with multple columns, for example, an index
> contains "State", "City" and "Street". Obviously the density of the column
s
> is State < City < Street. Is it necessary to follow a particular order whe
n
> create index for better performance? For example (State, City, Street) or
> (Street, City, State)?|||Adam,
I am a little bit confused. If I create the following index:
use northwind
go
create nonclustered index ix_nc_employees_country_city_postalcode on
employees(country, city, postalcode)
go
dbcc show_statistics (employees, ix_nc_employees_country_city_postalcode)
go
I see statistics for:
- country
- country, city
- country, city, postalcode
how does this apply to "index statistics are only computed for the first
column in the index"?
if I do:
set showplan_text on
go
select
*
from
dbo.employees
where
country = 'usa'
and city like 's%'
go
set showplan_text on
go
the stimated execution plan shows an index seek in
ix_nc_employees_country_city_postalcode.
Thanks in advance,
Alejandro Mesa
"Adam Machanic" wrote:

> Yes, it can have a huge impact. Higher selectivity = less rows traversed
=
> better performance. Also remember that index statistics are only computed
> for the first column in the index; statistics play a large part when the
> optimizer does its job, so it's important to keep that in mind.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
> columns
> when
>
>|||Sorry Mike, but the order of the logical expressions in the where clause is
not important. SQL Server does not evaluate them in a specific order. What I
think is important is that the columns in the where clause follow this
pattern (the order of the logical expressions is not important):
...
where state ...
-- or
where state ... and city ...
-- or
where state ... and street ...
-- or
...
where state ... and city ... and street ...
if you filter by (city, street) then sql server will not implement an index
seek in this index.
Regards,
Alejandro Mesa
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Based on your where clause.
> If you have an index on State, City, Street and use a where clause City,
> State, Street, the index will not be used. In this case, SQL can not guess
> that state is of any use, and it then can't use the index for the sort.
> Maybe and Index on state, a separate one on city, and a separate one on
> street.
> A compound index is generally a bad idea as a data modification will resul
t
> in a lot of index maintenance, slowing down the operation.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
>
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:327FEF28-39BC-4B5C-991F-612139545D77@.microsoft.com...
> I see statistics for:
> - country
> - country, city
> - country, city, postalcode
> how does this apply to "index statistics are only computed for the first
> column in the index"?
I probably phrased that poorly. Should have been, "index statistics are
based on the first column in the index." Which, I hope, serves to imply
that no statistics will be computed on only the second column, or only the
second and third columns, etc. The first column is the key.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||If you create the composite index described, SHOW_STATISTICS will show you
density info for each subset of keys, but the density info is only the
average number of duplicates across all values. Following the density info
you should see the statistics, which is the histogram on number of
occurences of specific values for the index key. That only applies to the
leading column of the index. Please see this whitepaper for more info:
http://msdn.microsoft.com/library/d...asp?frame=true
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:327FEF28-39BC-4B5C-991F-612139545D77@.microsoft.com...[vbcol=seagreen]
> Adam,
> I am a little bit confused. If I create the following index:
> use northwind
> go
> create nonclustered index ix_nc_employees_country_city_postalcode on
> employees(country, city, postalcode)
> go
> dbcc show_statistics (employees, ix_nc_employees_country_city_postalcode)
> go
> I see statistics for:
> - country
> - country, city
> - country, city, postalcode
> how does this apply to "index statistics are only computed for the first
> column in the index"?
> if I do:
> set showplan_text on
> go
> select
> *
> from
> dbo.employees
> where
> country = 'usa'
> and city like 's%'
> go
> set showplan_text on
> go
> the stimated execution plan shows an index seek in
> ix_nc_employees_country_city_postalcode.
>
> Thanks in advance,
> Alejandro Mesa
> "Adam Machanic" wrote:
>