Having many indices requires SQL Server to maintain and update many indices for each insert, delete or update statement. So having less indices is generally better.
However, when you have a compound index (made up of multiple columns), that only helps if you use/specify the n left-most columns in your query.
So if you have an index on (City, LastName, FirstName) for an address table, that index might be used if
- you have all three columns in your
WHERE clause
- you specify
WHERE City = 'London' in your query (using the 1 left-most column)
- you specify
WHERE City = 'London' and LastName = 'Smith' in your query (using the 2 left-most columns)
However, such an index cannot ever be used for:
- finding all rows with a
WHERE FirstName = 'Joe' clause - you're not using the n left-most columns in that index
- finding all rows with a
WHERE LastName = 'Brown' AND FirstName = 'Charlie' clause - again: you're not using the n left-most columns in that index
A compound index cannot (in many cases) replace all one-column indices. Be very careful when designing and creating compound indices to make sure you design them in a way they'll really be useful!
So while compound indices do have their reason to be, and they're generally preferable - you also need to make sure they'll really be able to be used. An index that is just being maintained (and causes effort on SQL Server's side), but can never be used in any queries, is the worst index ever - no gain, only maintenance overhead.
You should also read Kimberly The Queen of Indexing Tripp's excellent blog posts on SQL Server indexing - most notably Indexes: just because you can, doesn't mean you should!
