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.

SET FMTONLY

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 NOTE:

Please do not confuse the FMTONLY feature with the following:

1. Query result options – Discard result after query executes – this does not return meta-data back to the client/calling application
   
2. OPTION (FAST n)/Table Hint: FASTFIRSTROW – this returns n number of data rows back to the client/calling application

Until we meet next time,

Be courteous. Drive responsibly.