set-forceplan-transact-sql.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	f1_keywords	helpviewer_keywords	dev_langs
SET FORCEPLAN (Transact-SQL)	SET FORCEPLAN (Transact-SQL)	WilliamDAssafMSFT	wiassaf	07/26/2017	sql	t-sql	reference	SET_FORCEPLAN_TSQL SET FORCEPLAN FORCEPLAN FORCEPLAN_TSQL	joins [SQL Server], overriding query optimizer process FORCEPLAN option SET FORCEPLAN statement query optimizer [SQL Server], optimizing process overriding query optimizer process	TSQL

SET FORCEPLAN (Transact-SQL)

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]

When FORCEPLAN is set to ON, the [!INCLUDEssNoVersion] 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.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

  
SET FORCEPLAN { ON | OFF }  

Remarks

SET FORCEPLAN essentially overrides the logic used by the query optimizer to process a [!INCLUDEtsql] SELECT statement. The data returned by the SELECT statement is the same regardless of this setting. The only difference is the way in which [!INCLUDEssNoVersion] processes the tables to satisfy the query.

Query optimizer hints can also be used in queries to affect how [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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)