A query that returns many rows may be optimized very differently, depending on the type of index and the number of rows to be returned. Some examples are:
Queries with search arguments that match many values, such as:
select title, price from titles where pub_id = "P099"
Range queries, such as:
select title, price from titles where price between $20 and $50
For queries that return a large number of rows using the leading key of the index, clustered indexes and covering nonclustered indexes are very efficient:
If the table uses allpages locking, and has a clustered index on the search arguments, the index is used to position the scan on the first qualifying row. The remaining qualifying rows are read by scanning forward on the data pages.
If a nonclustered index or the clustered index on a data-only-locked table covers the query, the index is used to position the scan at the first qualifying row on the index leaf page, and the remaining qualifying rows are read by scanning forward on the leaf pages of the index.
If the index does not cover the query, using a clustered index on a data-only-locked table or a nonclustered index requires accessing the data page for each index row that matches the search arguments on the index. The matching rows may be scattered across many data pages, or they could be located on a very small number of pages, particularly if the index is a clustered index on a data-only-locked table. The optimizer uses data row cluster ratios to estimate how many physical and logical I/Os are required to read all of the qualifying data pages.
