The From Clause Documentation starting with SQL Server 2008 briefly mention 3 join hints and their basic mechanisms:

  • Reduce
  • Replicate
  • Redistribute

However there does not seem to be much information on when it might become necessary to use them.

It appears that they can be used in conjunction with the hash, loop and merge which are already understood for purpose of this question.

The relevant section from the documentation:

For SQL Data Warehouse and Parallel Data Warehouse, these join hints apply to INNER joins on two distribution incompatible columns. They can improve query performance by restricting the amount of data movement that occurs during query processing. The allowable join hints for SQL Data Warehouse and Parallel Data Warehouse are as follows:

    Reduces the number of rows to be moved for the table on the right side of the join in order to make two distribution incompatible tables compatible. The REDUCE hint is also called a semi-join hint.

    Causes the values in the joining column from the table on the left side of the join to be replicated to all nodes. The table on the right is joined to the replicated version of those columns.

    Forces two data sources to be distributed on columns specified in the JOIN clause. For a distributed table, Parallel Data Warehouse will perform a shuffle move. For a replicated table, Parallel Data Warehouse will perform a trim move. To understand these move types, see the "DMS Query Plan Operations" section in the "Understanding Query Plans" topic in the Parallel Data Warehouse product documentation. This hint can improve performance when the query plan is using a broadcast move to resolve a distribution incompatible join.

  • In testing under SQL Server, a potentially negative side-effect is that join order is enforced by the use of these hints. – Max Vernon Jan 24 '19 at 21:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.