Avoiding unnecessary sort operations

Sort operations in a query plan are very expensive, so we need to avoid anything that might introduce a sort where it is not needed. Using ORDER BY in our query practically guarantees a sort unless we happen to be able to leverage an index and an ordered scan.

Tip

If your query needs to produce an ordered result set and uses a covering index, ensure the index sort order is the same as the query’s desired order. This will increase the likelihood that the SQL Database Engine can leverage the index to order the rows rather than having to do a costly sort operation.

This may be necessary if we need our result set to be returned in a specific order, but if order is not important, this is just overhead.

In this section, we will look at a few examples that may introduce an unnecessary sort operation.

UNION ALL versus UNION

The UNION and UNION ALL syntax is used to combine the results of two separate queries into a single result...