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,

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

Is SET ROWCOUNT a Depreciated Feature?

I have read in some blog post which are saying the SET ROWCOUNT feature is a depreciated feature in SQL Server 2012.

This is half truth.

SET ROWCOUNT for INSERT, UPDATE, and DELETE statements will be depreciated but not for SELECT. 

If you want to us INSERT / UPDATE / DELETE with SET ROWCOUNT, you need to use TOP key word.