Costing for queries using order by

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:

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” for more information.

Besides the availability of indexes, two major factors determine whether the index is considered: