如何在不显示结果的情况下执行 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6386587/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Execute SQL Query without Displaying results
提问by Rajesh Kumar
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 中。
采纳答案by gbn
Executing willreturn 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...
虽然个人从未使用过它......
Edit 2018. As noted, see @deroby's answer for a better solution these days
编辑 2018 年。如前所述，这些天请参阅@deroby 的答案以获得更好的解决方案
回答by deroby
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 ONLYis 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.
您可能想要这样做的原因是为了避免等待和浪费资源以将结果加载到网格中，但仍然能够拥有例如实际执行计划。
回答by Philip Kelley
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 dataresults 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 只能接受在使用“结果集太多”消息停止查询之前，来自给定“运行”的这么多结果集。[嗯，请仔细检查我的“仅查询计划”位——我认为它会这样做，但它'
回答by Anthony Faull
Perhaps the interviewer intended to ask a different question:
也许面试官打算问一个不同的问题：
How would you execute a SQL query without returning the number ofresults?
如何在不返回结果数的情况下执行 SQL 查询？
In that case the answer would be SET NOCOUNT ON.
在那种情况下，答案是SET NOCOUNT ON。
回答by Alex K.
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.
解析但不执行，因此不返回任何内容。
回答by Cogitator
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，然后删除临时表。
回答by Trevor Best
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() 函数都不会导致转换错误等。因此抑制实际数据不是一个选项，显示不是太糟糕但有点浪费资源，如果只以文本形式发送结果，最好不要重复。
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
回答by Dennis Traub
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
回答by user9129053
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 包，它被用作加载数据仓库表的数据源。
回答by S.E.
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).
另一个用例是当您只想读取表的所有行时，例如对损坏进行测试。在这种情况下，您不需要数据本身，只需要它是否可读的事实。但是，选项名称“在执行后丢弃结果”有点令人困惑 - 它告诉我结果已被提取，然后才被丢弃。相反，它肯定会获取数据，但不会将其存储在任何地方（默认情况下，将行放入网格中，或您选择的任何输出中） - 接收到的行会即时丢弃（而不是在执行后丢弃）。
