IT:AD:SQL Server:HowTo:SQL:What SET NOCOUNT ON does
Summary
For Performance reasons, it should be the first line of any Stored Proc.
Notes
SET NOCOUNT ON should be the first line of any Stored Proc.
It turns off the messages that SQL Server sends back to the client after each CRUD T-SQL statement is executed.
This info is handy when you run a T-SQL statement in a query window, but in a stored proc, useless. By removing this extra overhead from the network it can greatly improve overall performance for your database and application.
If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option.
By issuing a SET NOCOUNT ON this function (@@ROWCOUNT) still works and can still be used in your stored procedures to identify how many rows were affected by the statement.