Cost model and requirements

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.

NoteIf a nonclustered index covers the result of a query, the optimizer does not consider using the nonclustered index hash-based scan.

See “Index covering” for more information about index covering.