It’s a great feeling when you, the kind reader, share your feedback on my posts because generally, these provide a lot of learning opportunities for both. In the last couple of weeks, I explored the following SET options:

1. PARSEONLY
2. NOEXEC 
3. FMTONLY (SQL Server 2008 R2 and below)
4. sp_describe_first_result_set – replacement to FMTONLY (SQL Server 2012 and above)

SET options allow us to change the behaviour of SQL Server for the current session with respect to handling of specific information like date and time values, locking, Query execution, transaction, ISO settings and Statistics. While the SET option for PARSEONLY is quite straight-forward, a confusion may arise between NOEXEC and FMTONLY. Here’s why:

• NOEXEC – parses a query, produces and adds to the plan cache the execution plan of the query/workload in hand, without executing the query
• FMTONLY – parses the query, produces the execution plan and executes the query/workload to return the meta-data associated to the query without processing any records

When the FMTONLY option is exercised, the query is actually executed to return the meta-data, but no records are processed. However, this behaviour is not intuitive. Here’s a follow-up question that I received in my post related to FMTONLY.

When you try to execute a query that have side effects (like an INSERT) with FMTONLY ON, you can easily check that the row is NOT inserted. I just tried this on SQL 2008 (not R2). Can you confirm this? If this is true, how is FMTONLY different from NOEXEC?

Demo

In order to answer this question, I have come up with the following demo. The demo simply involves using the NOEXEC and FMTONLY options on a query around a non-existent table – MyTable. Since the table is non-existent, I should encounter a run-time error the moment the query is executed. The script below does the following:

• Sets the system to a base state where both NOEXEC and FMTONLY are set to OFF
• A query around the non-existent table – MyTable is executed with the NOEXEC option turned ON
• The same query is re-executed with the FMTONLY option turned ON

USE tempdb
GO

--Base State
SET NOEXEC OFF
GO
SET FMTONLY OFF
GO
PRINT '00. Setting base state - both NOEXEC and FMTONLY are now OFF.'
GO

PRINT '01. Now checking whether MyTest already exists or not.'
PRINT '    If the ObjectId is not printed after this line, object is non-existent.'
PRINT OBJECT_ID('MyTest')


PRINT '02. Running with NOEXEC ON now...'
--Set the NOEXEC option to ON, telling the database engine
--to Parse and Compile the query, but not execute it
SET NOEXEC ON
GO

--Test workload.
--NOTE: The MyTest table does not exist!
SELECT Id,
       MyValue
FROM MyTest

SET NOEXEC OFF
GO

PRINT '03. NOEXEC is now OFF.'
PRINT '04. Running with FMTONLY ON now...'

--Set the FMTONLY option to ON, telling the database engine
--to Parse, Compile, Execute the query, 
--and return meta-data, but not return any records
SET FMTONLY ON
GO

--Test workload.
--NOTE: The MyTest table does not exist!
SELECT Id,
       MyValue
FROM MyTest

SET FMTONLY OFF
GO

PRINT '05. FMTONLY is now OFF.'
GO

Running the entire script against my test SQL Server instance (tested on SQL 2008, SQL 2008 R2 and SQL 2012) yields the following results.

00. Setting base state – both NOEXEC and FMTONLY are now OFF.

01. Now checking whether MyTest already exists or not.

    If the ObjectId is not printed after this line, object is non-existent.

 
02. Running with NOEXEC ON now…

03. NOEXEC is now OFF.

04. Running with FMTONLY ON now…

Msg 208, Level 16, State 1, Line 4

Invalid object name ‘MyTest’.

Conclusion

The above was a very simple demo, with a very clear output which helped me arrive at the following conclusion:

• Although the table is non-existent (as confirmed by the OBJECT_ID() call in step #01), NOEXEC did not return any errors
  • This indicates that NOEXEC did not actually “submit” the query to the query engine for execution
• The  attempt with the FMTONLY option set to ON however, did return a runtime error and that resulted in batch termination (no statements after that point were executed)
  • This indicates that FMTONLY did execute the query and as a result, encountered the error

Now, because the attempt with FMTONLY resulted in a batch termination, allow me to return my server back to the base state.

(To know more about SQL Server error states, please visit Pinal Dave, a.k.a. SQLAuthority’s [B|T] post: Introduction to SQL Error Actions – A Primer)

--Base State
SET NOEXEC OFF
GO
SET FMTONLY OFF
GO
PRINT '00. Setting base state - both NOEXEC and FMTONLY are now OFF.'
GO

I trust that the above demo answers all questions that one may have regarding the differences between NOEXEC and FMTONLY. If you have any other questions, please feel free to send them over to me. I will be glad to help!

Until we meet next time,

Be courteous. Drive responsibly.