Physical Join Operators in SQL Server – Merge Operator

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 .

Query 6

Execution Plan 6

*Exercise: In the demo code I’ve added two more examples (Queries 6b and 6c) where a small change to the query changes the physical operator chosen by the SQL Server optimizer. Try to play around with the parameters and see how the optimizer changes its decisions.

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 .

Text Box: SELECT	SOD.SalesOrderID,
            FROM		Sales.SalesOrderDetail AS SOD
            INNER JOIN
            Production.Product AS P
            ON SOD.UnitPrice = P.Listprice
            WHERE		SOD.OrderQty > 5
            ORDER BY 	P.ListPrice DESC

Query 7

Execution Plan 7

DISCLAIMER: No birds (nor any other animals) were harmed for the making of this script. As a matter of fact, three of my cats actually assisted the writing of this article. One is purring in my lap as I’m writing these lines…

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 joins.

Pages: 1 2

Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |