How to processes a join in the same order as the tables appear in the FROM clause of a query by using set forceplan in sql server
Sometimes we may need to process the table join in the same order as they appear in the FROM clause. To do this SET FORCEPLAN ON can be used to change the normal SQL Server execution plan. When this clause is used joins will take place in a natural order how tables appear in the FROM clause. This statement will be applied when we execute our SQL script.
With FORCEPLAN off the query execute normally with Merge Join.
With FORCEPLAN on the query execute in natural order as it appears in the FROM clause and it uses Nested Loops
Read More:
http://technet.microsoft.com/en-us/library/ms188344.aspx
http://technet.microsoft.com/en-us/library/aa259224(v=sql.80).aspx
