One may encounter many situations wherein the database objects (an ad-hoc query, stored procedures, functions, etc) need to supply meta-data to the calling applications so that the format of the response can be tested without actually running the query. A real-world example is to ensure that the application is working with the correct version of the database. If the number or type of the columns returned are not as per expectations, the user can be presented with a well-formed message rather than an exception. I believe ADO uses this mechanism to make a “dry-run”.
In SQL Server 2008 R2 and below, one SET option comes in very handy in realizing this requirement. This option is the FMTONLY option.
NOTE: The FMTONLY option is marked as a deprecated feature in SQL Server 2012. I will be writing about the replacement in the immediate future.
At execute time, if the SQL Server database engine encounters the option FMTONLY as being set to ON, no rows are processed or sent to the client. However, the query/batch is parsed (PARSEONLY), compiled (NOEXEC) and executed. Here’s a simple demo script:
USE AdventureWorks2012 GO --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 SELECT '3' AS RoundNum, Employee.BusinessEntityID, Employee.BirthDate, Employee.Gender, Employee.JobTitle FROM HumanResources.Employee AS Employee GO SET FMTONLY OFF --Check for caching SELECT '3' AS RoundNum, usecounts, cacheobjtype, objtype, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE usecounts > 0 AND text like '%FROM HumanResources.Employee%' AND text NOT LIKE '%Check%' ORDER BY usecounts DESC; GO
Executing the query returns two result sets – first one from the actual query (indicating that the query was actually executed), and the second result set showing that the query plan was compiled.
The messages tab supports the observation:
Please do not confuse the FMTONLY feature with the following:
- Query result options – Discard result after query executes – this does not return meta-data back to the client/calling application
- OPTION (FAST n)/Table Hint: FASTFIRSTROW – this returns n number of data rows back to the client/calling application
Until we meet next time,
Nakul, are you sure about the “and executed” bit?
This is not my experience. In fact, 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?
@TIA Cris: That’s a great question! Yes, FMTONLY does indeed execute the query, but it does not process any records – only the meta-data is retrieved. Inserting data into another table would require processing of records, which FMTONLY does not do.
I have developed a demo around this question – about which I will be writing on early next week (post of October 29, 2012). Stay tuned!
Thank-you very much for your feedback and the question!
Thank you Nakul! I’m already waiting for your next post!