Every time a SQL statement is executed it returns the number of rows that were affected. And this can lead to two problems:
· As more than necessary data is sent over a network, if the network was already saturated, SQL Server overall performance would suffer.
· If the execution ratio is high, the additional data stored in the client machine memory will affect its performance.
By using “SET NOCOUNT ON” within your stored procedure you can shut off these messages and reduce some of the traffic.
This eliminates the printed message of (xx rows(s) affected) in the Query Analyzer window. It also eliminates a message of DONE_IN_PROC that is communicated from SQL Server to the client application, which causes another round trip across the network.
Let’s take a look at a stored procedure with “SET NOCOUNT ON”:
ALTER PROCEDURE usp_GetUser AS SET NOCOUNT ON SELECT TOP 10 ID,Title,Firstname,LastName FROM [user] SELECT @@ROWCOUNT AS RowsReturned SET NOCOUNT OFF EXEC usp_GetUser
here is the result we get:
To know the effect of “SET NOCOUNT ON” on @@ROWCOUNT , you can have a quick look at this: http://blog.sqlauthority.com/2011/12/02/sql-server-effect-of-set-no-count-on-rowcount/