Improve SQL Server Stored Procedure Performance by using SET NOCOUNT ON

Provided by the FMS Development Team

In Microsoft SQL Server, when executing a Transact-SQL statement, the number of "rows affected" is returned by default.

For example, execute the following in SQL Analyzer:

USE Pubs
BEGIN
  UPDATE authors
  SET Contract = 1
  WHERE State = 'KS'
END

when this query is executed, the messages pane displays:

(1 row(s) affected)

Because additional processing time is needed to ascertain the number of rows affected, turning off this feature reduces the time needed to process the stored procedure. This is accomplished by using the SET NOCOUNT ON command.

Add it to the start of the example and when executed, notice that the rows affected message is suppressed.

USE Pubs
BEGIN
SET NOCOUNT ON
  UPDATE authors
  SET Contract = 1
  WHERE State = 'KS'
END

We agree the terminology is odd that to turn off the feature, one needs turn ON the NOCOUNT command. The reverse is also true. To reactivate the rows affected, use SET NOCOUNT OFF.