Microsoft SQL Server 2012 Performance Tuning Cookbook

Understanding Hash, Merge, and Nested Loop Join strategies

SQL Server uses three physical join operators, listed as follows, to interpret the query you execute:

• Hash Join
• Merge Join
• Nested Loop Join

None of the physical join operators are the "best" or "worst" for all situations. SQL Server 2012 chooses appropriate operator to perform query in an appropriate way. Join operators are being used in SQL Server from earlier versions and is still available in SQL Server 2012 without any change.

Let us have short introduction of each join operator:

• Hash Join: SQL Server chooses Hash Join as a physical operator for query in case of high volume of data that is not sorted or indexed. Two processes together make the Hash Join, which are Build and Probe. In Build ...