Is it possible that Execute SQL Query without Displaying results?
是否可以在不显示结果的情况下执行SQL查询？
like
喜欢
Select * from Table_Name
after running this query result should not be displayed in sql server.
运行此查询后，结果不应该显示在sql server中。
11 个解决方案
#1
29
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抑制行但不抑制结果集
SET FMTONLY ON
SELECT * FROM sys.tables
SET FMTONLY OFF
SELECT * FROM sys.tables
Never had a use for it personally though...
虽然没有亲自使用它...
#2
44
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.
我很惊讶没有人想出答案：打开“执行后丢弃查询结果”选项;我很确定那是采访者所追求的。 SET FMT ONLY是完全不同的东西恕我直言。
In SSMS
在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.
您可能希望这样做的原因是为了避免必须等待并浪费资源以便将结果加载到网格中但仍然能够例如实际执行计划。
#3
13
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.)
正如@gbn所说，一种编程方式是使用SET FMTONLY（谢谢，现在我不必从我的旧脚本文件中挖掘它）。一些程序和实用程序在查询SQL时执行此操作;首先他们提交一个带有FMTONLY ON的查询，以确定结果表结构的布局，然后当他们准备好运行它以FMTONLY OFF获得时，获取实际数据。 （当程序调用第二个程序时，我发现了这一点，第二个程序返回了数据集，并且由于不明原因，整个纸牌屋都掉了下来。）
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.]
这也可以在SSMS中完成。对于所有查询窗口，在“工具/选项”下，“查询结果/ SQL Server /结果到XX”，选中“查询执行后丢弃结果”;对于当前窗口，在“查询/查询选项”下，“结果/ XX”，相同的复选框。这里的优点是查询将在数据库服务器上运行，但不会返回数据结果。如果您正在检查查询计划但不希望收到10GB的数据（通过网络连接到您的笔记本电脑上），或者您正在进行一些严重的循环测试，这可能非常有用，因为SSMS只能接受在使用“太多结果集”消息停止查询之前，来自给定“运行”的结果集如此之多。 [嗯，请仔细检查一下“仅查询计划”位 - 我认为这样做了，但已经很长时间了。
#4
6
insert anothertable
Select * from Table_Name
Executes the select but returns nothing
执行select但不返回任何内容
set noexec on
Select * from Table_Name
Parses but does not execute and so returns nothing.
解析但不执行，因此不返回任何内容。
#5
6
Perhaps the interviewer intended to ask a different question:
也许面试官打算提出一个不同的问题：
How would you execute a SQL query without returning the number of results?
如何在不返回结果数的情况下执行SQL查询？
In that case the answer would be SET NOCOUNT ON.
在这种情况下，答案将是SET NOCOUNT ON。
#6
2
Is the goal to suppress all rows? Then use a filter that evaluates to false for every row:
目标是压制所有行吗？然后使用对每行计算结果为false的过滤器：
SELECT * FROM Table_Name WHERE 1 = 2
#7
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.
就我而言，我正在测试数据在所有视图中的表现，例如：任何cast（）函数都没有导致转换错误等。所以压制实际数据不是一个选项，显示不是太糟糕但是浪费了一点资源，如果只在文本中发送结果，最好不要diplsay。
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.
我想出了以下脚本以这种方式测试所有视图，唯一的问题是当它遇到具有text / ntext列的视图时。
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
#8
2
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.
如果您需要执行查询但不需要实际的结果集，则可以将查询包装在EXISTS（或NOT EXISTS）语句中：IF EXISTS（SELECT * FROM TABLE_NAME ...）。或者，您可以选择INTO #temp，然后再删除临时表。
#9
0
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).
另一个用例是当你只想读取表的所有行时，例如测试是否存在损坏。在这种情况下，您不需要数据本身，只需要它是否可读。但是，选项名称“在执行后丢弃结果”有点令人困惑 - 它告诉我结果被取出然后才被丢弃。相反，它肯定会获取数据但不会将其存储在任何位置（默认情况下，行被放入网格中，或者您选择的任何输出） - 接收到的行会被动态丢弃（而不是AFTER执行）。
#10
0
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.
我很惊讶社区不能轻易找到这个用例。大型结果集会在客户端上占用内存，如果许多SSMS窗口处于活动状态，这可能会成为一个问题（我打开2-3个SSMS实例并不常见，每个实例都有50-70个活动窗口）。在某些情况下，就像在Cyril的例子中一样，SSMS可能会耗尽内存而无法处理大型结果集。例如，当我需要调试返回数亿行的存储过程时，我遇到了一个案例。在不丢弃结果的情况下，在我的开发机器上运行SSMS是不可能的。该过程适用于SSIS包，它用作加载数据仓库表的数据源。 SSMS中的调试涉及进行非功能性更改（因此结果集对我不感兴趣）并检查执行统计信息和实际查询执行计划。
#11
-2
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:
我需要一个proc来返回指定用户在某个特定时间点之后更新的所有记录，仅显示记录存在的结果。这里是：
-- 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
#1
29
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抑制行但不抑制结果集
SET FMTONLY ON
SELECT * FROM sys.tables
SET FMTONLY OFF
SELECT * FROM sys.tables
Never had a use for it personally though...
虽然没有亲自使用它...
#2
44
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.
我很惊讶没有人想出答案：打开“执行后丢弃查询结果”选项;我很确定那是采访者所追求的。 SET FMT ONLY是完全不同的东西恕我直言。
In SSMS
在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.
您可能希望这样做的原因是为了避免必须等待并浪费资源以便将结果加载到网格中但仍然能够例如实际执行计划。
#3
13
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.)
正如@gbn所说，一种编程方式是使用SET FMTONLY（谢谢，现在我不必从我的旧脚本文件中挖掘它）。一些程序和实用程序在查询SQL时执行此操作;首先他们提交一个带有FMTONLY ON的查询，以确定结果表结构的布局，然后当他们准备好运行它以FMTONLY OFF获得时，获取实际数据。 （当程序调用第二个程序时，我发现了这一点，第二个程序返回了数据集，并且由于不明原因，整个纸牌屋都掉了下来。）
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.]
这也可以在SSMS中完成。对于所有查询窗口，在“工具/选项”下，“查询结果/ SQL Server /结果到XX”，选中“查询执行后丢弃结果”;对于当前窗口，在“查询/查询选项”下，“结果/ XX”，相同的复选框。这里的优点是查询将在数据库服务器上运行，但不会返回数据结果。如果您正在检查查询计划但不希望收到10GB的数据（通过网络连接到您的笔记本电脑上），或者您正在进行一些严重的循环测试，这可能非常有用，因为SSMS只能接受在使用“太多结果集”消息停止查询之前，来自给定“运行”的结果集如此之多。 [嗯，请仔细检查一下“仅查询计划”位 - 我认为这样做了，但已经很长时间了。
#4
6
insert anothertable
Select * from Table_Name
Executes the select but returns nothing
执行select但不返回任何内容
set noexec on
Select * from Table_Name
Parses but does not execute and so returns nothing.
解析但不执行，因此不返回任何内容。
#5
6
Perhaps the interviewer intended to ask a different question:
也许面试官打算提出一个不同的问题：
How would you execute a SQL query without returning the number of results?
如何在不返回结果数的情况下执行SQL查询？
In that case the answer would be SET NOCOUNT ON.
在这种情况下，答案将是SET NOCOUNT ON。
#6
2
Is the goal to suppress all rows? Then use a filter that evaluates to false for every row:
目标是压制所有行吗？然后使用对每行计算结果为false的过滤器：
SELECT * FROM Table_Name WHERE 1 = 2
#7
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.
就我而言，我正在测试数据在所有视图中的表现，例如：任何cast（）函数都没有导致转换错误等。所以压制实际数据不是一个选项，显示不是太糟糕但是浪费了一点资源，如果只在文本中发送结果，最好不要diplsay。
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.
我想出了以下脚本以这种方式测试所有视图，唯一的问题是当它遇到具有text / ntext列的视图时。
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
#8
2
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.
如果您需要执行查询但不需要实际的结果集，则可以将查询包装在EXISTS（或NOT EXISTS）语句中：IF EXISTS（SELECT * FROM TABLE_NAME ...）。或者，您可以选择INTO #temp，然后再删除临时表。
#9
0
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).
另一个用例是当你只想读取表的所有行时，例如测试是否存在损坏。在这种情况下，您不需要数据本身，只需要它是否可读。但是，选项名称“在执行后丢弃结果”有点令人困惑 - 它告诉我结果被取出然后才被丢弃。相反，它肯定会获取数据但不会将其存储在任何位置（默认情况下，行被放入网格中，或者您选择的任何输出） - 接收到的行会被动态丢弃（而不是AFTER执行）。
#10
0
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.
我很惊讶社区不能轻易找到这个用例。大型结果集会在客户端上占用内存，如果许多SSMS窗口处于活动状态，这可能会成为一个问题（我打开2-3个SSMS实例并不常见，每个实例都有50-70个活动窗口）。在某些情况下，就像在Cyril的例子中一样，SSMS可能会耗尽内存而无法处理大型结果集。例如，当我需要调试返回数亿行的存储过程时，我遇到了一个案例。在不丢弃结果的情况下，在我的开发机器上运行SSMS是不可能的。该过程适用于SSIS包，它用作加载数据仓库表的数据源。 SSMS中的调试涉及进行非功能性更改（因此结果集对我不感兴趣）并检查执行统计信息和实际查询执行计划。
#11
-2
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:
我需要一个proc来返回指定用户在某个特定时间点之后更新的所有记录，仅显示记录存在的结果。这里是：
-- 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