sql server - Multiple Indexes vs Multi-Column Indexes -

i've been adding index table in sql server 2005 , got me thinking. difference between creating 1 index , defining multiple columns on having 1 index per column want index.

are there reasons why 1 should used on other?

for example

create nonclustered index ix_indexname on tablename (column1 asc, column2 asc, column3 asc) 

versus

create nonclustered index ix_indexname1 on tablename (column1 asc)  create nonclustered index ix_indexname2 on tablename (column2 asc)  create nonclustered index ix_indexname3 on tablename (column3 asc) 

i agree cade roux.

this article should on right track:

• indexes in sql server 2005/2008 – best practices, part 1
• indexes in sql server 2005/2008 – part 2 – internals

one thing note, clustered indexes should have unique key (an identity column recommend) first column. helps data insert @ end of index , not cause lots of disk io , page splits.

secondly, if creating other indexes on data , constructed cleverly reused.

e.g. imagine search table on 3 columns

state, county, zip.

• you search state only.
• you search state , county.
• you search state, county, zip.

then index state, county, zip. used in 3 of these searches.

if search zip alone quite lot above index not used (by sql server anyway) zip third part of index , query optimiser not see index helpful.

you create index on zip alone used in instance.

i guess answer looking depends on clauses of used queries , group by's.

the article lot. :-)