I was recently watching a videos online of TechEd 2011 USA (link) and I learned that SET FMTONLY ON is going to be replaced with enhanced DMVs in future versions of SQL Server. I really liked the new direction of the product. However, SET FMTONLY ON is really have done its job so far. I have used it many times so far and always find it useful.
SET FMTONLY ON returns only metadata to the client. It can be used to test the format of the response without actually running the query. When this setting is ON the resultset only have headers of the results but no data. If resultset has Spatial Results, it will have the spatial results tab as well, however, no spatial data.
USE AdventureWorks2008R2
GO
SET FMTONLY ON;
SELECT *
FROM HumanResources.Department;
SELECT *
FROM Person.Address;
SET FMTONLY OFF;
GO
If you have turned on the execution plan (CTRL+M) while executing this settings, it will not return any execution plan as well.
Reference: Pinal Dave (https://blog.sqlauthority.com)
19 Comments. Leave new
Hi Pinal,
is it helpfull in select * into statements
or
creating the schema of empty datatable in dot net…
One time, at a client, I noticed that many events in the profiler were showing with suspiciously high amount of reads.
I looked into that, and noticed that the bactches were preceded with FMTONLY statements, and in profiler both events had as much reads.
At the time I was surprised, since the FMTONLY ON should have a few reads, since it just returns the metadata, not the entire resultset.
Could you please explain what the processing routine of a FMTONLY statement looks like – i.e. is the statement parsed, algebrized, optimized and so on?
Thank you.
Thanks
Is there any difference if I use WHERE 1=2 ?
This is most stupid feature ever. ADO components sometimes add SET FMTONLY ON and make dry run.
But FMTONLY ignore logical statements, so this is VALID part of code:
CREATE PROCEDURE dbo.RunSomeCode(@Param VARCHAR(10))
AS
IF 1=2 — YEAH we are in FMTONLY
BEGIN
SET FMTONLY OFF
— OR EXIT procedure
END
..
..
otherwise:
1)
SELECT
a, b
INTO
#TABLE
FROM
SomeTable
SELECT * FROM #Table -> FAIL, because #Table will not be created
2)
— @Foo = ”
SET @mark = CHARINDEX(‘X’, @Foo)
IF @mark > 0 — WITH FMTONLY this will pass
BEGIN
SET @SubStr = SUBSTRING(.. ) — Error here
END
Hi Pinal Also what do you think about this to get column names?
SELECT * FROM Table WHERE 1=0
I don’t have to mess with the SET OPTIONS and the execution plan is doing/does a constant scan ( I also tested it on a 10 TB table)
Yes thats the simple solution without worrying much on SET OPTIONS
Hi Pinal, I Am Not Understanding That What’s Use Of It In Practical Scenario. In Which Situation It’s Helpful to The Developer also?
If you want to know the column names quickly it is helpful
Hi Pinal – I found it very useful to get the ADO.NET DataSet Schema fill to work – and the LINQ2SQL designer to work – where I have used a temporary table in a stored procedure. In this case you may get an error message saying the temporary table could not be found – after some digging around I came across the FMTONLY option and hence your post.
I found that if I set the set fmtonly off; just before I created a temporary table and set fmtonly on; just after I created the table then the schema could be read quite happily (I had joined on the temporary table for the final SELECT) – with both ADO.NET DataSets and the LINQ2SQL designer. Apparently the ADO.NET and LINQ tools uses the FMTONLY option internally against the entire stored procedure – my fix just overrode it enough to allow temp table to be parsed as well.
To expand a bit on what Paul said,
If you’re using an ORM mapper (we use ADO.NET entity framework) sometimes it will not be able to see the result set properly when you’re trying to setup your mappings. If you watch it in profiler, the framework will open a connection to the DB, SETS FMTONLY ON, then runs the proc with all NULL values passed in for the parameters.
It works most of the time, but I occasionally Entity Framework will just report an empty result set. I haven’t seen any pattern to my SQL code when this happens, but the only fix I’ve come across so far is to add SET FMTONLY OFF within the procedure’s definition and redeploy the SQL code.
Hi Pinal, is there a way I could get a “vertical” list of columns..I am interested in just the number of columns being returned..It’s like saying “Is there an equivalent of count(*) to count the number of columns returned by a stored procedure”?
Select into a temp table, say #mytemp, and do this:
SELECT COLUMN_NAME FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE ‘#mytemp%’
And you will get a vertical list of the column names. You can add metadata or whatever else from INFORMATION_SCHEMA as well, of course.
Hi pinal , Inside set FMT ony on does not allow Go Statement .What i do
For ex:
Set Fmt only on
Select * from Tables1
Go
Update table1 set id =@id where name =@name
Go
I got Error .what i do
Paul – Thank you very much for sharing this post. Been struggling with Entity Framework in VS because of this.
Hi Pinal,
I use an MDX DataSource, can I use “SET FMTONLY ON” along with my MDX query? If not, could you please suggest any workarounds please?
Hi what is the purpose of
SET FMTONLY ON/OFF ?
We can still see the “Estimated Execution plan” of the query if this option is ON.
Hi, I stumbled across this page while looking for a solution to a problem- I have a T-SQL query built by joining 10 different tables and Views.
A Client wants a delimited file extract with Text Qualifier “Quotes” only on the data records and not on the Header( Field names).
I did this for now by using a DFT with a New column in the Derived Column task with the Field Names as Row records
ex: New_Field “Column1,Column2…”
Is there a way to capture just the Field names using T-sql?