Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Select from a table with no rows returned.

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.

Comments

Posted by Anonymous on 5 July 2011

Pingback from  Dew Drop – July 5, 2011 | Alvin Ashcraft's Morning Dew

Posted by lugnplu on 5 July 2011

Highlight the table and make sure your session is in the context of the database the table resides in.

Alt + F1 runs sp_help on the table and gives you a lot of lovely information about the table including the columns and what types they are.

SET FMTONLY ON is no more in Denali, so this will work only in versions 2008 R2 and below that have it.

msdn.microsoft.com/.../ms173839%28v=SQL.110%29.aspx

Posted by Anonymous on 10 July 2011

Pingback from  Select random records using TABLESAMPLE clause

Leave a Comment

Please register or log in to leave a comment.