Composite database indexes

I'm looking for confirmation of my understanding of composite indexes in databases - specifically in relation to SQL Server 2008 R2, if that makes a difference.

I think I understand that the order of the columns of the index is crucial in that if I have an index of { [Name], [Date] }, then a SELECT based on a WHERE clause based on [Date] won't be able to use the ind开发者_Python百科ex, but an index of { [Date], [Name] } would. If the SELECT is based on both columns, either index would be usable.

Is that right? What are the benefits of using a composite index like this, over two indexes on each column (i.e. { [Date] }, and { [Name] }).

Thanks!

---

Not quite, a selection on date could still use the index but not as effective as a query including name as name would limit how much of the index has to be searched.

If you often have queries on name + date and date and name seperate, use 3 indexes one for each combo.

Also having the most varied field first in an index also faster limits the index seach amound making it faster.

You can also have included columns, data thats not indexed but that is ofter fetched based on the index.

---

That is correct.

A composite index is useful when the combined selectivity of the composite columns prunes the result set effectively.

If you add 'INCLUDED' columns to an index (composite or non-composite), you can create a 'covering' index to cover a query (or queries), which is desireable as it removes the need to perform a second lookup to obtain those columns (from the clustered index).

The choice of two single column indexes OR a composite index of the combined columns is determined by the total query workload against that table.