设置 NOCOUNT OFF 或返回 @@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
我知道两者都有效，但哪个是更好的选择，为什么？
经过一番尝试后，我得出的结论是，SET NOCOUNT 在存储过程中默认为 OFF。 是否可以更改我的数据库内的这种行为？
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?
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
使用@@RowCount。 它是明确且透明的，它完全由您的代码而不是内置行为控制。
NOCOUNT选项可以手动设置为默认为ON（选项>查询执行>SQL Server>高级）。 如果您以这种方式设置它，但随后在存储过程中声明SET NOCOUNT OFF，则本地设置优先。Use @@RowCount. It's explicit and transparent, it is entirely controlled by your code rather than a built-in behaviour.
The
NOCOUNToption can be manually set to default toON(Optons>Query Execution>SQL Server>Advanced). If you set it this way but then declareSET NOCOUNT OFFin your stored procedure then that local setting takes precedence.不要对值使用 RETURN。 按照惯例，存储过程的 RETURN 用于错误代码，0 表示没有错误，非 0 表示存在某种问题。 如果您需要返回数据，适当的方法是使用 OUTPUT 参数。 基于其他语言对 return 的使用，这有点违反直觉。
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.
我知道 SET NOCOUNT ON 会让 DataAdapter 认为存在并发冲突。
您可以在 MSDN 上阅读相关内容。 如果代码将由 DataAdapter 使用，那么显然不要使用 SET NOCOUNT ON。
看起来 SqlCommand 也有这种行为，我猜这就是 DataAdapter 有问题的原因（因为在幕后它将使用 Command 对象）。
I know that having SET NOCOUNT ON would make a DataAdapter think there was a concurrency conflict.
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.
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).
使用 SET NOCOUNT ON/OFF 的原因：
Reasons for using SET NOCOUNT ON/OFF:
为什么我们使用 SET NOCOUNT on/off ---
Ans：我们可以通过以下步骤来理解这一点：
步骤 1：执行查询“Select top 10 * from table name”。
步骤 2：打开消息窗口，它显示一条消息“10 rows受影响”。 它会产生额外的开销并延长我们的执行时间。
步骤 3：为了克服这种额外的开销，我们使用 SET NOCOUNT ON。 如果它是On，那么它永远不会计算返回的行数，而是会传播命令已成功完成的消息。
步骤 4：默认情况下，NOCOUNT 为 ON，然后它会计算返回的行数，这就是为什么我建议在创建新过程期间关闭它，以便从数据库服务器获得更好的性能。
Why we use SET NOCOUNT on/off ---
Ans : we can understand this by following steps
step 1 : execute query "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.
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.
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.