Now, SQL Server can dynamically switch between the two types of joins. Note that adaptive joins always choose between nested loop and hash joins. Merge joins are not currently part of the heuristics.

Join Algorithms There are several types of join algorithms that the SQL Server optimizer can choose from, including nested loop join, hash join, and merge join. The choice of the join algorithm ...

However, cross-partition operations, such as joins, require data repartitioning, leading to significant communication overhead. To address this challenge, we propose Merge Hash Join (MHJ), a novel SQL ...