Syntax
SET NOCOUNT { ON | OFF }
Remarks
When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.
The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.
SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.
The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.
以sp_who为例做下解释:
SET NOCOUNT OFF
EXEC sp_who
SET NOCOUNT ON
EXEC sp_who
可以看出除了返回我们所想要的results的外,SET NOCOUNT OFF,还返回所影响的行数,SET NOCOUNT ON,则只返回SQL执行成功.
这样做的好处:
提高了SQL执行性能,优化项目。