SET FORCEPLAN
Makes the Microsoft® SQL Server™ query optimizer process a join in the same order as tables appear in the FROM clause of a SELECT statement only.
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 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.
The setting of SET FORCEPLAN is set at execute or run time and not at parse time.
Permissions
SET FORCEPLAN permissions default to all users.
Examples
This example performs a join between three 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.
-- SET SHOWPLAN_TEXT to ON.
SET SHOWPLAN_TEXT ON
GO
USE pubs
GO
-- Inner join.
SELECT a.au_lname, a.au_fname, t.title
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON ta.title_id = t.title_id
GO
-- SET FORCEPLAN to ON.
SET FORCEPLAN ON
GO
-- Reexecute inner join to see the effect of SET FORCEPLAN ON.
SELECT a.au_lname, a.au_fname, t.title
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON ta.title_id = t.title_id
GO
SET SHOWPLAN_TEXT OFF
GO
SET FORCEPLAN OFF
GO