Importance of SET NOCOUNT in Stored Procedures

                    Today , I am going to share one of important concept .It sounds very small but it is very important.Whenever we write any procedure and execute it a message appears in message window that shows number of rows affected with the statement written in the procedure.

e.g

CREATE PROCEDURE Test_no_count

AS

    SET NOCOUNT OFF

  BEGIN

      SELECT 1

      SELECT 2

      SELECT 3

      SELECT 4

  END 

GO

Exec Test_no_count

GO

Go
    Default value is SET NOCOUNT OFF.We don't need to specify OFF.For demo purpose I have specified explicitly.

If it is off then it will return affected row message as marked circle sometime affected row count will be multiple  of 100s so this message will creates an extra overhead on the network. 

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.

When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.

GO

CREATE PROCEDURE Test_no_count

AS

    SET nocount On

  BEGIN

      SELECT 1

      SELECT 2

      SELECT 3

      SELECT 4

  END 

GO

Exec Test_no_count 

GO

     Important thing is @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

           SET NOCOUNT ON statement can be useful in store procedures. 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.