Monday, April 1, 2013

SET FMTONLY

This is the eight post of SET Statement series

when SET FMTONLY set to ON, query will return only metadata to the client. This setting can be used to test the format of the response without actually running the query.

USE AdventureWorks2012;
GO
SET FMTONLY ON
;
GO
SELECT
*
FROM Production.Product;
GO
SET FMTONLY OFF
;
GO

Output of this is,


image


So there will be no rows returned.


However, this SET option is set to depreciate with SQL Server 2012.


Previous posts of this series,


SET IMPLICIT_TRANSACTIONS ON


SET NOCOUNT


SET DEADLOCK_PRIORITY


SET CONCAT_NULL_YIELDS_NULL


SET FORCEPLAN


SET PARSEONLY


SET IDENTITY_INSERT

1 comment:

  1. of course, you can always revert to the old tried and true...

    select * from dbo.whatever where 0=1

    ...and get the same result. :)



    By Michelle Poolet From SQL DBA Support Linkedin Group

    ReplyDelete