How to Execute SQL Query without Displaying results_问答_开发者

Is it possible that Execute SQL Qu开发者_Python百科ery without Displaying results?

Select * from Table_Name

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

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.

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

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.]

insert anothertable
Select * from Table_Name

Executes the select but returns nothing

set noexec on 
Select * from Table_Name

Parses but does not execute and so returns nothing.

Perhaps the interviewer intended to ask a different question:

How would you execute a SQL query without returning the number of results?

In that case the answer would be SET NOCOUNT ON.

If you need the query to execute but don't need the actual resultset, you can wrap the query in an EXISTS (or NOT EXISTS) statement: IF EXISTS(SELECT * FROM TABLE_NAME...). Or alternately, you could select INTO #temp, then later drop the temp table.

Is the goal to suppress all rows? Then use a filter that evaluates to false for every row:

SELECT * FROM Table_Name WHERE 1 = 2

In my case I was testing that the data was behaving in all views, e.g. any cast() functions weren't causing conversion errors, etc. so supressing the actual data wasn't an option, displaying wasn't too bad but a bit of wasted resource and better not to diplsay if sending results only in text.

I came up with the following script to test all the views in this way, the only problem is when it encounters views that have text/ntext columns.

declare csr cursor local for select name from sys.views order by name
declare @viewname sysname
declare @sql nvarchar(max)

open csr
fetch next from csr into @viewname
while @@fetch_status = 0 begin
    --set @sql = 'select top 1 * from ' + @viewname 

    set @sql = 'declare @test nvarchar(max) select @test = checksum(*) from ' + @viewname 

    print @viewname

    exec sp_executesql @sql
    fetch next from csr into @viewname
end
close csr
deallocate csr

If you are using PostgreSQL you can put your select in a function and use PERFORM The PERFORM statements execute a parameter and forgot result.

A PERFORM statement sets FOUND true if it produces (and discards) one or more rows, false if no row is produced.

https://www.postgresql.org/docs/9.1/plpgsql-statements.html#:~:text=A%20PERFORM%20statement%20sets%20FOUND,if%20no%20row%20is%20returned.

Yet another use case is when you just want to read all the rows of the table, for example testing against corruptions. In this case you don't need the data itself, only the fact that it is readable or not. However, the option name "Discard results AFTER execution" is a bit confusing - it tells me that the result is fetched and only then discarded. In contrary, it fetches the data for sure but does not store it anywhere (by default the rows are put into the grid, or whatever output you have chosen) - the received rows are discarded on the fly (and not AFTER execution).

I am surprised the community can't easily find a use case for this. Large result sets take memory on the client, which may become a problem if many SSMS windows are active (it is not unusual for me to have 2-3 instances of SSMS opened, each with 50-70 active windows). In some cases, like in Cyril's example, SSMS can run out of memory and simply unable to handle a large result set. For instance, I had a case when I needed to debug a stored procedure returning hundreds of millions of rows. It would be impossible to run in SSMS on my development machine without discarding results. The procedure was for an SSIS package where it was used as a data source for loading a data warehouse table. Debugging in SSMS involved making non-functional changes (so the result set was of no interest to me) and inspecting execution statistics and actual query execution plans.

I needed a proc to return all records updated by a specified user after a certain point in time, only showing results where records existed. Here it is:

-- Written by David Zanke
-- Return all records modified by a specified user on or after a specified date. 


If mod date does not exist, return row anyhow

Set Nocount on 

Declare @UserName varchar(128) = 'zanked'
    , @UpdatedAfterDate Varchar( 30)  = '2016-10-08'
    , @TableName varchar( 128)
    , @ModUser varchar( 128)
    , @ModTime varchar( 128)
    , @sql varchar( 2000 )


-- In a perfect world, left join would be unecessary since every row that captures the last mod user would have last mod date.

-- Unfortunately, I do not work in a perfect world and rows w/ last mod user exist w/o last mod date

Declare UserRows Cursor for Select distinct c1.table_name, c1.column_name, c2.column_name  From INFORMATION_SCHEMA.COLUMNS c1

Left Join INFORMATION_SCHEMA.COLUMNS c2 On c1.Table_Name = c2.Table_Name And c2.Column_name like '%DTTM_RCD_LAST_UPD%'
        Where c1.column_name like '%UPDATED_BY_USER%'


Open UserRows

Fetch UserRows Into  @tablename, @ModUser, @ModTime

While ( @@FETCH_STATUS = 0 )
Begin
    -- capture output from query into a temp table

    Select @sql = 'Select ''' + @TableName + ''' TableName, * Into ##HoldResults From ' + @TableName + ' Where ' + @ModUser + ' = ''' + @userName + ''''
            + Case When @ModTime Is Null Then '' Else   ' And ' + @ModTime + ' >= ''' + @UpdatedAfterDate + '''' End

    Exec ( @sql)

    -- only output where rows exist
    If @@ROWCOUNT > 0
    Begin
        Select * from ##HoldResults
    End
    Drop Table ##HoldResults

    Fetch UserRows Into  @tablename, @ModUser, @ModTime

End
Close UserRows;
Deallocate UserRows