Merge Joins are another technique used by SQL Server to join tables with a large number of records. A merge join works effectively when the data from both tables is sorted in the same order. It takes the data from each table and works through both tables simultaneously, moving forward in either table when the value from the opposite table is larger.
This allows the join to proceed with the utmost efficiency in that records from either table are only evaluated once, and only the joined values are retained in the output result set.
Because a merge join requires the data to be sorted in the same order it may not be the most efficient technique. While it works great with large sets of data, if it is not already sorted then it must first sort the data and then perform the merge.
You can improve the performance of your merge joins through indexing. If both tables being merged already contain an index with the correct sorting the merge process may begin immediately without a pre-join step of sorting the data. The index may be used in place of the clustered index. Covering indexes are even more efficient.
When you join two tables with a lot of data look for a merge join in your query plan, especially if the data is already sorted the same on both tables.
Cheers,
Ben