SET FMTONLY ON Changes SQL Control-of-Flow Statement Processing

The other day, my friend Jonathan (of Camenisch Creative) surprised me by pointing out that SET FMTONLY ON changes the way Microsoft SQL Server processes control-of-flow statements (IF…ELSE…END, GOTO, RETURN, etc.)

When SET FMTONLY is OFF (the default), SQL Server processes control-of-flow statements normally. In the case of an IF statement, SQL Server determines which conditional expression evaluates to true and then executes the code associated with that expression. Turn FMTONLY to ON and SQL Server executes every conditional branch, even those associated with conditions evaluating to false!

To illustrate, if SET FMTONLY is OFF, executing the following SQL returns one result set containing a column named 1 = 1. With SET FMTONLY ON, the below SQL returns two result sets: one containing the already-mentioned 1 = 1 column and a second containing a column named 1 = 2. When FMTONLY is ON, the code blocks associated with both of these conditional expressions are executed—even though one of the conditions evaluates to false!

IF 1 = 1
	SELECT '1 = 1' = 'Value'
ELSE IF 1 = 2
	-- Normally, this SELECT statement would not be executed because 1 does not equal 2.
	SELECT '1 = 2' = 'Value'
END

Setting SET FMTONLY ON also changes the behavior of CONTINUE, RETURN and BREAK statements, TRY…CATCH blocks and WAITFOR statement as demonstrated by the following SQL:

SET FMTONLY ON;

DECLARE @counterContinue INT;
SET @counterContinue = 1;

WHILE (@counterContinue < 10) BEGIN
	SET @counterContinue = @counterContinue + 1;
	CONTINUE;
	-- Normally, this next SELECT statement will never be executed because of the proceeding
	-- CONTINUE; however, SET FMTONLY ON causes it to be processed.
	SELECT 'Post Continue Table' = 'Value';
END

DECLARE @counterBreak INT;
SET @counterBreak = 1;

WHILE (@counterBreak < 10) BEGIN
	SET @counterBreak = @counterBreak + 1;
	BREAK;
	-- If SET FMTONLY where OFF, this next SELECT statement wouldn't be run but
	-- because SET FMTONLY is ON, it will be executed.
	SELECT 'Post Break Table' = 'Value';
END

BEGIN TRY
	SELECT 'Try Table Column' = 'Value'
END TRY
-- Normally, this CATCH statement will only be executed if an error occurs in the TRY
-- statement block. With SET FMTONLY ON, the CATCH block will always be executed.
BEGIN CATCH
	SELECT 'Catch Table Column' = 'Value'
END CATCH

-- With SET FMTONLY ON, the no waiting occurs.
BEGIN
    WAITFOR DELAY '02:00';
    SELECT 'Wait' = 'Value'
END;

-- If SET FMTONLY is OFF, the code below this RETURN statement will never be executed.
-- When SET FMTONLY ON, the following SELECT statement is executed.
RETURN
AfterReturnLabel:
	SELECT 'After Return Label Table' = 'Value'

Why does FMTONLY ON cause this behavior change?

Here’s my guess: SET FMTONLY ON is used to retrieve metadata describing the result set(s) that could be returned when the specified query is run (i.e. run with SET FMTONLY OFF). In order for SQL Server to provide metadata on all result sets that could be returned, SQL Server must process all branches of all control-of-flow statements.