SQL Server Merge Join Operator – Part2

Hi Geeks,

I hope you liked yesterday’s post SQL Server Merge Join operator Part1. This operator requires an equality operator and inputs sorted in join predicate. In our example from yesterday, the join predicate has an equality operator and is using SalesOrderID column which are clustered indexes in respective tables and is sorted by SalesOrderID as it is the clustering key.

As inputs are sorted, merge join reads reads a row simultaneously from each of its inputs and compares them. If the row matches, they are returned and is not the smaller value is discarded since both inputs are sorted. This process continues until one of the tables is completed. Cost of this operator is the sum of both the inputs.

In case inputs are not sorted, the query optimizer may choose a different operator. As an example, following query will use a nested loop join.

USE [AdventureWorks2012]

SELECT EMP.BusinessEntityID
FROM HumanResources.Employee EMP
	INNER JOIN Sales.SalesPerson AS SLS
	ON EMP.BusinessEntityID = SLS.BusinessEntityID





In the example, Employee table is sorted on BusinessEntityID column whereas but not on Sales table which can be verified from ToolTip and hence query optimizer chosen nested loop join and not merge join operator.

Query optimizer is most likely to choose this operator for medium to large inputs and there is an equality operator on the predicate which are sorted on both the inputs.

That’s it for today, see you tomorrow.

Happy learning!



Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook


About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published.