Merge join with Non Clustered Indexes
Merge will be very efficient when one or both tables have a
non clustered index that sorts the join column. This is twice as true if the
index covers the query, as in Query 6 .
ORDER BY clause may coax a Merge join
As I mentioned earlier, in many cases you will see that the
optimizer decides to sort one or both inputs just to use the merge operator.
This will usually happen when the inputs are not very large and the
alternatives are worse. This decision becomes even easier if the optimizer sees
that performing a sort provides an additional benefit, allowing the optimizer
to ‘kill two birds with one stone’ (see disclaimer below)
For example, the pre sorting may help facilitate the highly efficient stream
aggregate for GROUP BY or DISTINCT clauses , UNION
operators, analytical rankings or for delivering the result set in the order of
the ORDER BY clause, as is the case in Query 7 .
In Part III we will conclude this series with a look at the Hash operator.
If you are interested to dive deeper and learn more
on joins and their implementation in SQL Server, I highly recommend Craig Freedman’s SQL Server
Blog on MSDN. Craig has published a series of
excellent, in depth articles regarding many more aspects and types of