Can SQL Server piece together two different indexes in a single-table query, rather than just giving up and scanning a suboptimal clustered index? The short answer is: yes, in a fairly narrow band of conditions.
Tag: seek
Reading a query plan
Knowing how to read a query plan is absolutely key to optimizing SQL Server query performance. The query plan tells you how SQL Server goes about running your query, including what indexes are used (and how), what join strategies are applied and a lot of other information. If you can read the query plan, you can make the appropriate changes to indexes, query hints, join conditions, etc to tune your workload for optimum performance.