Best practices on setting NOCOUNT

One accepted SQL Server best practice is to SET NOCOUNT ON to avoid unnecessary DONE_IN_PROC messages being sent to the client. Although this may not have a noticeable performance benefit on many systems with few users, it can mount up to significant overhead when you have a higher number of users and many statements within a stored procedure.

You can SET NOCOUNT ON for all connections by changing the default user options using sp_configure or through the connection tab on the instance properties. This may seem like an easy way to do this, but you relying on this being set is not a robust solution for example Biztalk can break if this setting is not on, but similarly on a shared server you may have an application that requires the setting to be off.

If you set the option for the connection when a user logs in is another option, but without knowing that this occurs, you may assume a different value when trying to debug an issue. This means that knowing how to find out what the user options are set is important. See my previous article on ways you can do this.

The method way that is not ambiguous is to SET NOCOUNT within the procedure. Setting NOCOUNT to be ON or OFF will make it clear to anyone who reads the code exactly what is expected, therefore regarless of the performance benefit it must be a worthwhile practice to do so.

Unfortunately if you just gone through all your code and added SET NOCOUNT ON to all the procedures, your problems may not be at an end! My next article describes one situation where you will still need to be careful.