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)?|||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
--|||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)?|||"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.com/composite_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 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)?|||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...
> > 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)?
>
>|||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 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)?
>
>|||So to maxmize the performance (without think about the update penalty), we
better create three index:
(state, city, street)
(City, Street)
(Street)
Or the following order is the same?
(Street, City, State)
(City, State)
(State)
will be used for all situation like
where state='...' and street = '...' and city='...' (Order doesn't matter?)
where street = ''
where city='' and street=''
but not "where sat
"Alejandro Mesa" wrote:
> 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 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)?
> >
> >
> >|||"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/default.asp?url=/library/en-us/dnsql2k/html/statquery.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...
> 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...
>> > 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)?
>>|||Thanks for the explanation.
AMB
"Adam Machanic" wrote:
> "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
> --
>
>|||Kalen,
Thanks a lot for the link. It helped me to understand the theme better.
Does Sql Server query optimizer use the density of the combination of
columns (in the case of multi-column statistics, or an index) in conjuntion
with the histogram to choose the most efficient plan?
Thanks in advance,
Alejandro Mesa
"Kalen Delaney" wrote:
> 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/default.asp?url=/library/en-us/dnsql2k/html/statquery.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...
> > 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...
> >> > 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)?
> >>
> >>
> >>
>
>|||Nick,
It depends on the selectivity of each column. Try using one index by each
column, test and see if it helps. If it does not help, then start doing
combinations (composite keys help to have better selectivity but at the
expense of more load when updating the keys), using in the most left side of
the key, the column with best selectivity or if the selectivity are similar
(or good selectivity), use the one you will use alone in a query.
Take the time to read the link posted by Kalen and all the good tips from
http://www.sql-server-performance.com/
AMB
"nick" wrote:
> So to maxmize the performance (without think about the update penalty), we
> better create three index:
> (state, city, street)
> (City, Street)
> (Street)
> Or the following order is the same?
> (Street, City, State)
> (City, State)
> (State)
> will be used for all situation like
> where state='...' and street = '...' and city='...' (Order doesn't matter?)
> where street = ''
> where city='' and street=''
> but not "where sat
> "Alejandro Mesa" wrote:
> > 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 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)?
> > >
> > >
> > >|||There may be cases where it uses both, but in general, if the optimizer has
a specific value to work with it uses the histogram, otherwise it uses the
density.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:9EA20A9A-CB5B-42E4-BABE-1FF889E2EDA1@.microsoft.com...
> Kalen,
> Thanks a lot for the link. It helped me to understand the theme better.
> Does Sql Server query optimizer use the density of the combination of
> columns (in the case of multi-column statistics, or an index) in
> conjuntion
> with the histogram to choose the most efficient plan?
>
> Thanks in advance,
> Alejandro Mesa
>
> "Kalen Delaney" wrote:
>> 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/default.asp?url=/library/en-us/dnsql2k/html/statquery.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...
>> > 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...
>> >> > 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)?
>> >>
>> >>
>> >>
>>|||Kalen,
Thanks again, I really appreciate your help.
Alejandro Mesa
"Kalen Delaney" wrote:
> There may be cases where it uses both, but in general, if the optimizer has
> a specific value to work with it uses the histogram, otherwise it uses the
> density.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:9EA20A9A-CB5B-42E4-BABE-1FF889E2EDA1@.microsoft.com...
> > Kalen,
> >
> > Thanks a lot for the link. It helped me to understand the theme better.
> >
> > Does Sql Server query optimizer use the density of the combination of
> > columns (in the case of multi-column statistics, or an index) in
> > conjuntion
> > with the histogram to choose the most efficient plan?
> >
> >
> > Thanks in advance,
> >
> > Alejandro Mesa
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> 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/default.asp?url=/library/en-us/dnsql2k/html/statquery.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...
> >> > 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...
> >> >> > 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)?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>