SET FORCEPLAN (Transact-SQL)

When FORCEPLAN is set to ON, the Microsoft 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, or they are requested with join hints or query hints.

Transact-SQL Syntax Conventions

Syntax

SET FORCEPLAN { ON | OFF }

Remarks

SET FORCEPLAN essentially overrides the logic used by the query optimizer to process a Transact-SQL SELECT statement. The data returned by the SELECT statement is the same regardless of this setting. The only difference is the way in which SQL Server processes the tables to satisfy the query.

Query optimizer hints can also be used in queries to affect how SQL Server processes the SELECT statement.

SET FORCEPLAN is applied at execute or run time and not at parse time.

Permissions

SET FORCEPLAN permissions default to all users.

Examples

The following example performs a join of four tables. The SHOWPLAN_TEXT setting is enabled, so SQL Server returns information about how it is processing the query differently after the SET FORCE_PLAN setting is enabled.

USE AdventureWorks;
GO
-- Make sure FORCEPLAN is set to OFF.
SET SHOWPLAN_TEXT OFF;
GO
SET FORCEPLAN OFF;
GO
SET SHOWPLAN_TEXT ON;
GO
-- Example where the query plan is not forced.
SELECT c.LastName, c.FirstName, v.Name
FROM Person.Contact AS c
   INNER JOIN HumanResources.Employee AS e
   ON e.ContactID = c.ContactID
   INNER JOIN Purchasing.PurchaseOrderHeader AS poh
   ON e.EmployeeID = poh.EmployeeID
   INNER JOIN Purchasing.Vendor AS v
   ON poh.VendorID = v.VendorID;
GO
-- SET FORCEPLAN to ON.
SET SHOWPLAN_TEXT OFF;
GO
SET FORCEPLAN ON;
GO
SET SHOWPLAN_TEXT ON;
GO
-- Reexecute inner join to see the effect of SET FORCEPLAN ON.
SELECT c.LastName, c.FirstName, v.Name
FROM Person.Contact AS c
   INNER JOIN HumanResources.Employee AS e 
   ON e.ContactID = c.ContactID
   INNER JOIN Purchasing.PurchaseOrderHeader AS poh
   ON e.EmployeeID = poh.EmployeeID
   INNER JOIN Purchasing.Vendor AS v
   ON poh.VendorID = v.VendorID;
GO
SET SHOWPLAN_TEXT OFF;
GO
SET FORCEPLAN OFF;
GO

See Also

Reference

SELECT (Transact-SQL)
SET (Transact-SQL)
SET SHOWPLAN_ALL (Transact-SQL)
SET SHOWPLAN_TEXT (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
5 December 2005	New content: Adjusted the wording of the introduction and added the forcing of a nested loop join.