SET FORCEPLAN (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
When FORCEPLAN is set to ON, the 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 }
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
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 AdventureWorks2022;
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 p.LastName, p.FirstName, v.Name
FROM Person.Person AS p
INNER JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = p.BusinessEntityID
INNER JOIN Purchasing.PurchaseOrderHeader AS poh
ON e.BusinessEntityID = poh.EmployeeID
INNER JOIN Purchasing.Vendor AS v
ON poh.VendorID = v.BusinessEntityID;
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 p.LastName, p.FirstName, v.Name
FROM Person.Person AS p
INNER JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = p.BusinessEntityID
INNER JOIN Purchasing.PurchaseOrderHeader AS poh
ON e.BusinessEntityID = poh.EmployeeID
INNER JOIN Purchasing.Vendor AS v
ON poh.VendorID = v.BusinessEntityID;
GO
SET SHOWPLAN_TEXT OFF;
GO
SET FORCEPLAN OFF;
GO
See Also
SELECT (Transact-SQL)
SET Statements (Transact-SQL)
SET SHOWPLAN_ALL (Transact-SQL)
SET SHOWPLAN_TEXT (Transact-SQL)