SET NOCOUNT ON
By default when you create stored procedure SQL Serer the SET NOCOUNT is ON.
In SQL server if you Set the NOCOUNT ON, you will not get count of number of rows affected by
Insert or Update or Delete queries.
When you set the NOCOUNT to OFF, your client application will get the count of number of rows affected
during the operations like Insert or Update or Delete.
How does it affect the performance?
The @@ROWCOUNT still updates even if you SET NOCOUNT ON. It only prevents sending of the message to client
about affected row number and it reduces the size of message (Reduces network traffic).