Why SQL Server uses Non-Clustered but not Clustered Index?
The optimizer has a choice between two main strategies:


Scan the table (the clustered index) checking every row to see if LoanNum = 2712.
Scan &amp; Lookup


Scan the nonclustered index to find rows where LoanNum = 2712
Look up the column data for the matched rows not covered by the nonclustered index.



The key point is that the nonclustered index is smaller, so scanning it is expected to be cheaper. This might seem counterintuitive because the clustered index definition has the same keys, and the nonclustered index has included columns, but the point is that the clustered index includes all columns stored in-row - the leaf (lowest) level of the clustered index literally is the in-row data.

For a small number of expected matches, the cost saving from scanning the smaller index is more than enough to compensate for the key lookups.

Incidentally, you may find that removing the WHERE 1 = 1 from your query causes the optimizer to choose the clustered index scan. The (redundant) constant-to-constant comparison prevents SQL Server parameterizing the query, so estimates are based on statistical information about LoanNum 2712. If the query is parameterized, SQL Server will use the average distribution of LoanNum values, which might result in a higher number of expected rows, and a change of plan choice.

See also:


Effective Clustered Indexes by Michelle Ufford
How (and why) does TOP impact an execution plan?