Indexes and data fetching

Indexes can speed up not only record lookup but data fetching too. If all fields that participate in a query are present in the index, database will not start retrieving actual table data but will use index values only. It is quite important factor to consider during table’s indexes tuning.
Example:


select maxof(Quantity) from table1
    where  table1.Key >= 100000
        && table1.Key <= 300000;

The query above runs around 320 ms on my machine with 1M records in Table1 when index on the Table1 contains only Key field. If Quantity field is added to the index execution time drops to 70 ms.

Note: It is important to mention that the difference appears only if lookup happens using non-cluster index. Otherwise query execution time will be almost the same because cluster index is stored together with table data and it is cheap to retrieve it.