How to Execute SQL Query without Displaying results

Is it possible that Execute SQL Query without Displaying results?

like

Select * from Table_Name

after running this query result should not be displayed in sql server.

Solution 1:

I'm surprised nobody came up with the answer : switch on the "discard query results after execution" option; l I'm pretty sure that was what the interviewer was after. SET FMT ONLY is totally different thing IMHO.

In SSMS

• open a new query
• in the menu select Query / Query options
• select the Results pane
• check the "discard result after execution"

The reason you might want to do this is to avoid having to wait and waste resources for the results to be loaded into the grid but still be able to have e.g. the Actual Execution Plan.

Solution 2:

Executing will return a recordset. It may have no rows of course but get a result

You can suppress rows but not the resultset with SET FMTONLY

SET FMTONLY ON
SELECT * FROM sys.tables

SET FMTONLY OFF
SELECT * FROM sys.tables

Never had a use for it personally though...

Edit 2018. As noted, see @deroby's answer for a better solution these days

Solution 3:

Sounds like a dubious interview question to me. I've done it, I've needed to do it, but you'd only need to do so under pretty obscure circumstances. Obscure, but sometimes very important.

As @gbn says, one programmatic way is with SET FMTONLY (thanks, now I don't have to dig it out of my old script files). Some programs and utilities do this when querying SQL; first they submit a query with FMTONLY ON, to determine the layout of the resulting table structure, then when they've prepared that they run it gain with FMTONLY OFF, to get the actual data. (I found this out when the procedure called a second procedure, the second procedure returned the data set, and for obscure reasons the whole house of cards fell down.)

This can also be done in SSMS. For all querying windows, under Tools/Options, Query Results/SQL Server/Results to XX, check "Discard results after query executes"; for only the current window, under Query/Query Options, Results/XX, same checkbox. The advantage here is that the query will run on the database server, but the data results will not be returned. This can be invaluable if you're checking the query plan but don't want to receive the resulting 10GB of of data (across the network onto your laptop), or if you're doing some seriously looped testing, as SSMS can only accept so many result sets from a given "run" before stopping the query with a "too many result sets" message. [Hmm, double-check me on that "query plan only" bit--I think it does this, but it's been a long time.]