Published 20 November 2017
JOIN HINT is used
It is not recommended to use join hints.
We can use the LOOP, HASH or MERGE join hints to force the optimizer to choose the indicated join method for the specific join operation to which the hint is applied. For example, ...FROM Table1 INNER HASH JOIN Table2...will force the use of a Hash Join operator, when joining Table1 and Table2. Application of the join hint applies to any query (SELECT, INSERT, or DELETE) where joins can be applied.
It is not a good idea to use join hints because the SQL Server query optimizer usually selects the most appropriate join operation, so they should only be used as a last resort by experienced developers and database administrators. Only use this hint if you are certain of the consequences for scalability and performance. You will also need a maintenance plan to identify and re-test all hints in response to changes in the data distribution, or an upgrade to a new SQL Server version, or application of a new service pack.
For more information on the dangers of using hints, see SQL Prompt Code Analysis: A Hint is Used (PE004-7).