Setting NOCOUNT to ON stops the message that shows the count of the number of rows affected by a T-SQL statement or stored procedure from being returned as part of the result set.
Why is this useful or important? It can result in a significant performance boost when running SQL statements:
“When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.
The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.
SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.
The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.” – from SQL Server online documentation