http://www.sqlservercentral.com/blogs/martin_catherall/2011/07/04/select-from-a-table-with-no-rows-returned_2E00_/

Printed 2014/12/21 02:26AM

Select from a table with no rows returned.

By Martin Catherall, 2011/07/04

G’day,

I recently ran into a nice feature that I had never encountered before.

Sometimes, when developing or administrating an unfamiliar table we will simply do a “SELECT *” so that we can quickly see the column names.

This is pretty much fine when the table is small.

However in the case where the table contains several million (or billion) records then SSMS will continue to return records to the tool for a long time.

The main way people use to prevent this is simply putting on a where clause that always evaluates to false, so in

USE AdventureWorks;
GO
SELECT *
FROM HumanResources.Employee
WHERE 1=2;
GO

However, did you know that you can set the “FMTONLY” option to ON to accomplish the same thing – like so

USE AdventureWorks;
GO
SET FMTONLY ON;
GO
SELECT *
FROM HumanResources.Employee
GO
SET FMTONLY OFF;
GO

Just a little tip that I thought I’d share.

Have a nice day.

cheers

Martin.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.