The SET FORCEPLAN statement overrides the logic used by the SQL Server query optimizer to process a T-SQL SELECT statement.

More specifically, when FORCEPLAN is set to ON, the query optimizer processes a join in the same order as the tables appear in the FROM clause of a query.

This also 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.

Example

To demonstrate how FORCEPLAN works, I’m going to run two SELECT queries, first with FORCEPLAN set to ON, then with FORCEPLAN set to OFF.

Both queries are identical, with the exception that the join tables are in listed a different order.

In this example I use SHOWPLAN_XML to show the estimated query plan, but you could just as easily use another method (such as the Explain button in Azure Data Studio, or the Include Actual Execution Plan icon in SSMS to display the actual query plan).

SET FORCEPLAN ON

SET FORCEPLAN ON;
GO

SET SHOWPLAN_XML ON;
GO

SELECT
    ar.ArtistName,
    al.AlbumName,
    g.Genre
FROM 
    Artists ar 
    INNER JOIN Albums al 
    ON ar.ArtistId = al.ArtistId 
    INNER JOIN Genres g 
    ON al.GenreId = g.GenreId;

SELECT
    ar.ArtistName,
    al.AlbumName,
    g.Genre
FROM 
    Albums al
    INNER JOIN Genres g 
    ON al.GenreId = g.GenreId
    INNER JOIN Artists ar 
    ON ar.ArtistId = al.ArtistId;

Result:

We can see that the query plan for each query reflects the order in which I included the table names in the FROM clause.

SET FORCEPLAN OFF

SET SHOWPLAN_XML OFF;
GO

SET FORCEPLAN OFF;
GO

SET SHOWPLAN_XML ON;
GO

SELECT
    ar.ArtistName,
    al.AlbumName,
    g.Genre
FROM 
    Artists ar 
    INNER JOIN Albums al 
    ON ar.ArtistId = al.ArtistId 
    INNER JOIN Genres g 
    ON al.GenreId = g.GenreId;

SELECT
    ar.ArtistName,
    al.AlbumName,
    g.Genre
FROM 
    Albums al
    INNER JOIN Genres g 
    ON al.GenreId = g.GenreId
    INNER JOIN Artists ar 
    ON ar.ArtistId = al.ArtistId;

Result:

This time, both queries result in an identical query plan. The query optimizer ignored the order in which I listed them in the FROM clause and determined its own order.

Note that the FORCEPLAN setting does not change the data returned by the SELECT statement. The actual results are the same regardless of whether FORCEPLAN is set to ON or OFF. The only difference is the way in which tables are processed (which could impact on performance).

You can use SET FORCEPLAN in conjunction with query optimizer hints to further affect how the query is processed.