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 process, it reads all rows from Build input (left-hand side input table) and creates an in-memory hash table based on the equijoin keys. In the Probe process, it reads all rows from the Probe input (right-hand side input table) based on equijoin...
