The cost model of a nonclustered index scan uses the formula:
The optimizer considers a hash-based index scan for any tables in a query that have useful nonclustered indexes, and for data-only-locked tables with clustered indexes. The query must also access at least 20 data pages of the table.
If a nonclustered index covers the result of a query,
the optimizer does not consider using the nonclustered index hash-based
scan.
See “Index covering” on page 291 in Performance and Tuning: Basics for more information about index covering.
