Set NOCOUNT ON/OFF ??

By using SET NOCOUNT we can remove this extra overhead from the network, that can actually improve the performance of our database and our application.

SET NOCOUNT ON statement into store procedures can reduce network traffic, because client will not receive the message indicating the number of rows affected by T-SQL statement. Setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

refer
http://www.dotnetfunda.com/interviews/show/2449/what-is-the-use-of-set-nocount-on-in-sql-server
https://ask.sqlservercentral.com/questions/1390/what-does-set-nocount-on-do.html

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Rb4bhushan, if this helps please login to Mark As Answer. | Alert Moderator

SET NOCOUNT means stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set. 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, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

You could find more information about SET NOCOUNT in below link.
http://msdn.microsoft.com/en-us/library/ms189837.aspx
https://sqlknowledgeshare.wordpress.com/2010/07/09/set-nocount-on-off-in-sql-server-2008/

rajesh.plsql123@gmail.com

Rb4bhushan, if this helps please login to Mark As Answer. | Alert Moderator

Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

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, or for procedures that contain Transact-SQL loops, 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.

To view the current setting for this setting, run the following query.

DECLARE @NOCOUNT VARCHAR(3) = 'OFF';
IF ( (512 & @@OPTIONS) = 512 ) SET @NOCOUNT = 'ON';
SELECT @NOCOUNT AS NOCOUNT;

SAMIR
Sr. Software Engineer

Rb4bhushan, if this helps please login to Mark As Answer. | Alert Moderator

SET NOCOUNT stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

https://msdn.microsoft.com/en-us/library/ms189837.aspx

Rb4bhushan, if this helps please login to Mark As Answer. | Alert Moderator