Queries that perform sorts for order by may create and sort, or they may be able to use the index to return rows by relying on the index ordering. For example, the optimizer chooses one of these access methods for a query with an order by clause:
With no useful search arguments – use a table scan, followed by sorting the worktable.
With selective search argument or join on an index that does not match the order by clause – use an index scan, followed by sorting the worktable.
With a search argument or join on an index that matches the order by clause – an index scan using this index, with no worktable or sort.
Sorts are always required for result sets when the columns in the result set are a superset of the index keys. For example, if the index on authors includes au_fname and au_lname, and the order by clause also includes the au_id, the query requires a sort.
If there are search arguments on indexes that match the order by clause, and other search arguments on indexes that do not support the required ordering, the optimizer costs both access methods. If the worktable and sort is required, the cost of performing the I/O for these operations is added to the cost of the index scan. If an index is potentially useful to help avoid the sort, dbcc traceon(302) prints a message while the search or join argument costing takes place.
See “Sort avert messages” on page 179 in the book Performance and Tuning: Monitoring and Analyzing for Performance for more information.
Besides the availability of indexes, two major factors determine whether the index is considered:
The order by clause must specify a prefix subset of the index keys.
The order by clause and the index must have compatible ascending/descending key ordering.
