SQL SET NOCOUNT OFF 还是 RETURN @@ROWCOUNT？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/995589/
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
SET NOCOUNT OFF or RETURN @@ROWCOUNT?
提问by niaher
I am creating a stored procedure in Sql Server 2008 database. I want to return the number of rows affected. Which is a better option SET NOCOUNT OFF or RETURN @@ROWCOUNT?
我正在 Sql Server 2008 数据库中创建一个存储过程。我想返回受影响的行数。SET NOCOUNT OFF 或 RETURN @@ROWCOUNT 哪个更好？
ALTER PROCEDURE [dbo].[MembersActivateAccount]
@MemberId uniqueidentifier
AS
BEGIN
-- Should I use this?
SET NOCOUNT OFF;
UPDATE [dbo].Members SET accountActive = 1 WHERE id = @MemberId;
--Or should I SET NOCOUNT ON and use the following line instead?
--return @@ROWCOUNT;
END
I know that both work, but which is a better choice and why?
我知道两者都有效，但哪个是更好的选择，为什么？
After some trying I am coming to a conclusion that SET NOCOUNT is OFF by default inside stored procedures. Is it possible to change this behavior inside my database?
经过一些尝试，我得出的结论是 SET NOCOUNT 在存储过程中默认为 OFF。是否可以在我的数据库中更改此行为？
回答by CJM
Use @@RowCount. It's explicit and transparent, it is entirely controlled by your code rather than a built-in behaviour.
使用@@RowCount。它是明确和透明的，它完全由您的代码而不是内置行为控制。
The NOCOUNToption can be manually set to default to ON(Optons>Query Execution>SQL Server>Advanced). If you set it this way but then declare SET NOCOUNT OFFin your stored procedure then that local setting takes precedence.
该NOCOUNT选项可以手动设置为默认值ON（选项>查询执行>SQL Server>高级）。如果您以这种方式设置它，然后SET NOCOUNT OFF在您的存储过程中声明，那么本地设置优先。
回答by onupdatecascade
Don't use RETURN for values. By convention RETURN from stored procedures is for error codes, 0 meaning no error and non-0 meaning some kind of problem. If you need data back, the appropriate way to do it is with an OUTPUT parameter. It's a little counter-intuitive based on other languages' use of return.
不要对值使用 RETURN。按照约定，存储过程的 RETURN 用于错误代码，0 表示没有错误，非 0 表示某种问题。如果您需要返回数据，适当的方法是使用 OUTPUT 参数。基于其他语言对 return 的使用，这有点违反直觉。
回答by RichardOD
I know that having SET NOCOUNT ON would make a DataAdapter think there was a concurrency conflict.
我知道 SET NOCOUNT ON 会使 DataAdapter 认为存在并发冲突。
You can read about it on MSDN. If the code is going to be used by DataAdapters then obviously don't use SET NOCOUNT ON.
您可以在 MSDN 上阅读它。如果代码将被 DataAdapters 使用，那么显然不要使用 SET NOCOUNT ON。
It looks like SqlCommand also has this behaviour, which I guess is the reason why the DataAdapter has a problem (as under the hood it will use a Command object).
看起来 SqlCommand也有这种行为，我猜这就是 DataAdapter 有问题的原因（因为在幕后它将使用 Command 对象）。
回答by Law Kant
Reasons for using SET NOCOUNT ON/OFF:
使用 SET NOCOUNT ON/OFF 的原因：
To control the stack overflow while inserting rows into any table. Passing the T-Sql messages while executing of the queries or nested queries. To Show or viewing the latest queries executed. To get information on the latest record escalation.
在将行插入任何表时控制堆栈溢出。在执行查询或嵌套查询时传递 T-Sql 消息。显示或查看最近执行的查询。获取有关最新记录升级的信息。
回答by Ashutosh
Why we use SET NOCOUNT on/off ---
为什么我们使用 SET NOCOUNT 开/关 ---
Ans : we can understand this by following steps
Ans：我们可以通过以下步骤来理解这一点
step 1 : execute query "Select top 10 * from table name".
第 1 步：执行查询“Select top 10 * from table name”。
step 2 : open message window it shows a message "10 rows affected". it creates extra overheads and extends our execution time.
第 2 步：打开消息窗口，它显示一条消息“受影响的 10 行”。它会产生额外的开销并延长我们的执行时间。
step 3 : to overcome this extra overheads we use SET NOCOUNT ON. If it is On then it will never count the number of row returns instead it sows a message commands completed successfully.
第 3 步：为了克服这种额外的开销，我们使用 SET NOCOUNT ON。如果它是 On 那么它永远不会计算行返回的数量，而是播发一条消息命令成功完成。
step 4 : By default NOCOUNT is ON then it counts the number of returned rows that is why my suggestion that it should off during creating new procedures to get better performance from database server.
第 4 步：默认情况下 NOCOUNT 为 ON，然后它计算返回的行数，这就是为什么我建议在创建新过程时它应该关闭以从数据库服务器获得更好的性能。
