A problem to be aware of when using the join hint in SQL Server

When we do query tuning for SQL Server, we often use option (Loop/merge/hash join) at the end of the statement or declare Loop/merge/hash directly before the join statement, To force SQL Server to use a particular type of join method. But there are times when a query optimizer-optimized execution plan may conflict with the way you declare a join, causing the execution plan to fail, let's take a look at the typical case below.

Declare @id1 int=1,@id2 int=1Select *  from [dbo].[T_people]aInner Join [dbo].[T_people_ii]B ona.ID=b.idwherea.id=@id1  andb.ID=@id2option(HashJoin)

We used the option prompt in the query statement above to force SQL Server to use a hash join to make a join query, and we get the following error message when we execute the statement:

Msg 8622, Level 16, State 1, line 3rd
The query processor could not generate a query plan because a hint was defined in this query. Resubmit the query, and do not specify any hints in the query, or use SET Forceplan.

Why does SQL Server produce such a message?

This is mainly because the query optimizer found the condition of the inner join when parsing the above statement

A.id=b.id

And then found it in the where condition.

[Email protected] and [email protected]

Then, in the query optimizer's view, @id1 and @id2 may be of any value of type int, or even @[email protected], such as @[email protected]=1

So this time the query optimizer will think that the inner join is completely redundant, so the above statement is optimized for cross join by the query optimizer, and then the a.id=1 and b.id=1 are filtered on this result.

Select *  from [dbo]. [t_peopl] a    Cross Join [dbo]. [T_people_ii] b

This time you observe the query plan of the above statement, found that there is a red fork in nested loops, indicating that the join is not predicate, that is, there is no join condition (cross join has no conditions), the term is called non-equijoin, In fact, in the following execution plan, both sides of the join index, as long as there is no predicate on one side, then the join is non-equijoin.

Instead of Equijoin, the Merge/hash join cannot be used, in other words, the cross join cannot be used with the Merge/hash join, only with loop join, so the top statement will be an error, requiring the option prompt to be removed. so SQL Server's prompt is not always available, even with the (index (...)) This mandatory use of the index prompt is also not easy to use, because some of the steps in the execution plan may conflict with the prompt you declare, resulting in the execution plan generation failure and error, according to the specific situation of the rational use of various SQL Server prompt.

A problem to be aware of when using the join hint in SQL Server