Monthly Archives: April 2016

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“.… More

Dynamic PIVOT query in SQL Server

PIVOT clause is used to generate cross tab outputs in SQL Server. We put unique data values from a column in the PIVOT clause to render them as multiple columns in aggregation with other columns required in the output. With the help of PIVOT clause, we can transpose the distinct values of a column into multiple columns. However, syntax of PIVOT clause requires these distinct values to be known at query design time. This kind of query can be considered as a static pivot query. We can hard code these distinct values in the PIVOT clause only if these values are fixed and static in nature.

However, when it comes to pivot uncertain values from a column, we would’t be able to cater these values in the PIVOT clause at query design time. Also, SQL Server does not provide any built in functionality which can be used in this scenario. … More