#0206 – SQL Server (2008 R2 & below) – SET FMTONLY – Parse, Compile & Execute a T-SQL query; return only metadata


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.


image


The messages tab supports the observation:


image


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.

Advertisements

3 thoughts on “#0206 – SQL Server (2008 R2 & below) – SET FMTONLY – Parse, Compile & Execute a T-SQL query; return only metadata

  1. profiles.google.com/103836085443746740503

    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

    Like

    Reply
  2. Nakul Vachhrajani

    @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!

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s