Improve performance of Stored Procedures by using SET NOCOUNT ON

Provided by:  FMS Development Team

In Microsoft SQL Server, when executing a Transact-SQL statement, the number of ‘rows affected’ is returned by default. As an example, execute the following in SQL Analyzer:

Use Pubs
Update authors
Set Contract = 1
WHERE State = 'KS'

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 can reduce the overall time needed to process a stored procedure. This can be accomplished by using the SET NOCOUNT ON command. Add it to the start of the above example, and when executed, notice that the rows affected message is suppressed. To reactivate the rows affected, use SET NOCOUNT OFF.

This tip and more detected by Total SQL Analyzer PRO

Return to the tips page