NBSteve (11/21/2014)

---

For those looking for more of an explanation:

While poorly framed, it appears the intent of the question is to determine which queries will perform a SEEK versus a SCAN. And while this is a complex subject, in this simplified example, the answer is it will do a SEEK when the WHERE clause uses at least the first column of the index.

The index is on (Fname, Lname) and the order the index is declared in is critical. A WHERE clause with only Lname can't use this index, however, a WHERE clause with only Fname, or a WHERE clause with both Fname and Lname can. (Note that the order in which the columns are listed in the WHERE clause does NOT matter, making queries 4 and 5 equivalent.)

Seemingly accidentally, the question also brings up the question of when the optimizer will use a NONCLUSTERED versus a CLUSTERED index. Also a complex subject, but in this case, because the NONCLUSTERED index is a covering index for all queries, the optimizer always chooses it.

A CLUSTERED index always includes all columns of a table, while the NONCLUSTERED index only includes its indexed columns (Fname, Lname), any INCLUDEd columns (none in this case) and the clustered index key (Empid), but not any remaining columns making it potentially smaller. In this case, there are no other columns, so the two indexes are equivalent and both contain the full table data. In general, a NONCLUSTERED index will be <= the CLUSTERED index in size, and so as long as it's a covering index (meaning it contains all data needed for the query, avoiding lookups into the clustered index) the optimizer will choose it over the clustered index as it should give equal-or-faster performance. (If the nonclustered index does not cover all fields needed for the query, this becomes far more complex as lookups to retrieve the additional columns are slow.)