Think of the index like the business section of the phone book.
There are multiple levels of sorts:
a
would be category, like "Doctors"
b
would be last name, like "Smith"
c
would be first name, like "John"
The listings (rows) are sorted in order of Category -> Last Name -> First name
Using this model, how can you find doctors named John
without knowing last names? The answer is, you can't, without looking at every single record (which means not using the index).
In your working examples, you use all the fields left to right without skipping any levels.
In your non-working example, you are looking for the last field and the first without knowing anything in the middle. You can't get to the last level without knowing what's in between.
Included columns don't prevent the index being used for any purpose.
The query optimizer will pick the best index (or possibly a table scan) that is the most efficient for the query being compiled. Keep indexes as small as possible, while still providing the desired functionality. According to Books Online INCLUDEd columns don't affect the query optimizer's selection of an index based strictly on size, however if any given index has all columns required for a query, either as key columns or included columns, that index will be given priority.
Since the query optimizer is a fairly opaque beast, you most likely need to test the suggested index to see if it is used, and what the resulting impact is for INSERTs, UPDATEs, and DELETEs.
Best Answer
There are a couple of reasons for this at present (I say at present, because improvements may be introduced in the future that reduce the relevance of these points):
This is true for most purposes and most query profiles. A counter example here would be a set of queries, regularly run, which would require a large number of compound indexes. The work being done to keep those indexes up to date, the amount of memory they take up might mean that index intersection is a better strategy. This, however, is an edge case - most database usage can be profiled and indexed efficiently so that index intersection is not required.