Multiple Indexes vs Multi-Column Indexes

Question

What is the difference between creating one index across multiple columns versus creating multiple indexes, one per column?

Are there reasons why one should be used over the 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) 

Answer 1

I agree with Cade Roux.

This article should get you on the right track:

• Indexes in SQL Server 2005/2008 – Best Practices, Part 1
• Indexes in SQL Server 2005/2008 – Part 2 – Internals

One thing to note, clustered indexes should have a unique key (an identity column I would recommend) as the first column. Basically it helps your data insert at the end of the index and not cause lots of disk IO and Page splits.

Secondly, if you are creating other indexes on your data and they are constructed cleverly they will be reused.

e.g. imagine you search a table on three columns

state, county, zip.

• you sometimes search by state only.
• you sometimes search by state and county.
• you frequently search by state, county, zip.

Then an index with state, county, zip. will be used in all three of these searches.

If you search by zip alone quite a lot then the above index will not be used (by SQL Server anyway) as zip is the third part of that index and the query optimiser will not see that index as helpful.

You could then create an index on Zip alone that would be used in this instance.

By the way We can take advantage of the fact that with Multi-Column indexing the first index column is always usable for searching and when you search only by 'state' it is efficient but yet not as efficient as Single-Column index on 'state'

I guess the answer you are looking for is that it depends on your where clauses of your frequently used queries and also your group by's.

The article will help a lot. :-)