Fifth post of SET Statement series.
Let us execute the following query.
SELECT SOH.SalesOrderID,SOH.OrderDate, P.ProductID, P.Name,SOD.OrderQty,SOD.LineTotal FROM Sales.SalesOrderDetail SOD
INNER JOIN Sales.SalesOrderHeader SOH ON SOH.SalesOrderID = SOH.SalesOrderID
INNER JOIN Production.Product P ON SOD.ProductID = P.ProductID
WHERE SOH.OrderDate = '2005-07-01'
AND P.ProductId = 712
Following will be the query execution plan.
You can see that query plan does not depends on the order of the tables in the query. for example, though you have SalesOrderDetail table in the query, query plan first pick the products table since that query plan will be the best.
If you set the above set option to ON, then the query plan will change. as shown below.
So when the SET FORCEPLAN is set to ON,SQL Server query optimizer processes a join in the same order as the tables appear in the FROM clause of a query. In addition, setting FORCEPLAN to ON forces the use of a nested loop join unless other types of joins are required to construct a plan for the query.
However,when using this option, you need to make sure that after setting it to ON, your query performs better.
Previous posts of this series,