设置NOCOUNT的最佳位置在哪里？

For a large database (thousands of stored procedures) running on a dedicated SQL Server, is it better to include SET NOCOUNT ON at the top of every stored procedure, or to set that option at the server level (Properties -> Connections -> "no count" checkbox)? It sounds like the DRY Principle ("Don't Repeat Yourself") applies, and the option should be set in just one place. If the SQL Server also hosted other databases, that would argue against setting it at the server level because other applications might depend on it. Where's the best place to SET NOCOUNT?

对于在专用SQL Server上运行的大型数据库(数千个存储过程),最好在每个存储过程的顶部包含SET NOCOUNT ON,或者在服务器级别设置该选项(属性 - >连接 - >“没有计数“复选框”?这听起来像DRY原则(“不要重复自己”),并且选项应该只在一个地方设置。如果SQL Server还托管了其他数据库,则会反对在服务器级别设置它,因为其他应用程序可能依赖于它。设置NOCOUNT的最佳位置在哪里?

2 个解决方案

#1

Make it the default for the server (which it would be except for historical reasons). I do this for all servers from the start. Ever wonder why it's SET NOCOUNT ON instead of SET COUNT OFF? It's because way way back in Sybase days the only UI was the CLI; and it was natural to show the count when a query might show no results, and therefore no indication it was complete.

使其成为服务器的默认设置(除了历史原因之外)。我从一开始就为所有服务器执行此操作。有没有想过为什么它是SET NOCOUNT ON而不是SET COUNT OFF?这是因为在Sybase的日子里,唯一的用户界面就是CLI;当查询可能没有显示结果时显示计数是很自然的,因此没有迹象表明它已完成。

#2

Since it is a dedicated server I would set it at the server level to avoid having to add it to every stored procedure. The only issue would come up is if you wanted a stored procedure that did not have no-count.

由于它是一个专用服务器,我会在服务器级别设置它,以避免必须将它添加到每个存储过程。唯一的问题是如果你想要一个没有计数的存储过程。