When to use composite indexes?

Question

What are the general rules in regards to using composite indexes? When should you use them, and when should you avoid them?

Answer 1

Composite indexes are useful when your SELECT queries use those columns frequently as criteria in your WHERE clauses. It improves retrieval speed. You should avoid them if they are not necessary.

This article provides some really good information.

Answer 2

A query that selects only a few fields can run completely on an index. For example, if you have an index on (OrderId) this query would require a table lookup:

select Status from Orders where OrderId = 42

But if you add a composite index on (OrderId,Status) the engine can retrieve all information it needs from the index.

A sort on multiple columns can benefit from a composite index. For example, an index on (LastName, FirstName) would benefit this query:

select * from Orders order by LastName, FirsName

Sometimes you have a unique constrant on multiple columns. Say for example that you restart order numbers every day. Then OrderNumber is not unique, but (OrderNumber, OrderDayOfYear) is. You can enforce that with a unique composite index.

I'm sure there are many more uses for a composite index, just listing a few examples.