Setting NOCOUNT ON for SQL Server

Usage and Config Platform
1

(imported topic written by MrFixit)

Are there negative implications of having this default setting changed for the SQL Server supporting BES databases? The Default is OFF.

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.

I didn’t see it used in any of the SPs that I scanned and it didn’t appear to have issue when I did a quick check on a lab system, but I thought I pose the question to the experts.

2

(imported comment written by SystemAdmin)

I would not recommend using ‘SET NOCOUNT ON’. Earlier, we found several queries and tools not working because they expected a result count to be returned and one of the effects of ‘SET NOCOUNT ON’ is to make it so queries don’t return the number of rows affected. We believe we fixed things to work even with ‘SET NOCOUNT ON’ but it is always possible we missed something and you’ll see queries fail. We don’t have a reason to think that this setting provides significant performance boosts for our database so I would recommend leaving counting on.