The join order has been enforced because a local join hint is used – Warning in SQL Server 3


When we use a local JOIN hint in T-SQL query, sometimes, SQL Server throws a warning message as “Warning: The join order has been enforced because a local join hint is used.“. For example, below is a query with a local join hint:

SELECT * FROM Person.Person A 
INNER MERGE JOIN HumanResources.Employee B 
ON A.BusinessEntityID = B.BusinessEntityID

After execution, we will get below warning message with result set:

Warning Message

Warning Message

In above code, we are using a local JOIN hint “INNER MERGE JOIN” which forces this query to override the physical join (which could be nested loop or merge or hash join) chosen by SQL Server with the physical join given in the query hint (which is MERGE join in this case). If you want to read more about the physical joins, visit my post “Nested loop, Merge, and Hash Joins in SQL Server“.

Here, SQL Server enforces a join order during query execution because a local join hint has been used in the query and it might throw a warning message as “Warning: The join order has been enforced because a local join hint is used.” also. Indeed, this is a warning and not an error but if we are executing this query from an application which keeps throwing an error for this warning message, we can force SQL Server to suppress this warning message during query execution.

To suppress this warning message, use “OPTION (FORCE ORDER)” at the end of the query as like below:

SELECT * FROM Person.Person A 
INNER MERGE JOIN HumanResources.Employee B 
ON A.BusinessEntityID = B.BusinessEntityID
OPTION(FORCE ORDER)

Using join hints is not recommended until it is explicitly required and it should be always used with caution.

If we are using a query hint which is executing the query more smoothly than the plan generated by SQL Server for the time being, in future, it might be a worst plan as in most of the cases, data keeps changing with respect of time.

Thank you for the reading.

Rate This
[Total: 1    Average: 5/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

Gopal has 8 years of industry experience in Software development. He has a head down experience in Data Science, Database, Data Warehouse, Big Data and cloud technologies and has implemented end to end solutions. He has extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). He also has good experience in windows and web application development using ASP.Net and C#.


Leave a comment

Your email address will not be published. Required fields are marked *

3 thoughts on “The join order has been enforced because a local join hint is used – Warning in SQL Server