Why do we use set nocount on in stored procedure

Set NOCOUNT: - This set statement stops the messages those show the count of no of rows affected by the T-SQL statements during the execution of stored procedures. We generally write this statement at the starting of the stored procedure. To stop the count of no of rows affected we write below statement.

Set NOCOUNT ON

To understand this let’s take an example of a stored procedure with set NOCOUNT option on.

I have created a stored procedure without setting up NOCOUNT on using below code.

Create procedure USP_NoCount

as

Begin

declare @tmp table(id int)

insert into @tmp values(1)

insert into @tmp values(1)

insert into @tmp values(1)

insert into @tmp values(1)

insert into @tmp values(1)

insert into @tmp values(1)

insert into @tmp values(1)

select * from @tmp

end

I am going to execute this stored procedure and lets see what happens.

After executing the stored procedure in management studio, I have clicked on messages tab indicated by arrow in the above snapshot. We can see that there are lot of messages which are indicating the no of rows affected. Now lets create a stored procedure with setting up NOCOUNT option on.

I am going to alter the stored procedure that we created earlier.

alter procedure USP_NoCount

as

Begin

      set nocount on

      declare @tmp table(id int)

      insert into @tmp values(1)

      insert into @tmp values(1)

      insert into @tmp values(1)

      insert into @tmp values(1)

      insert into @tmp values(1)

      insert into @tmp values(1)

      insert into @tmp values(1)

      select * from @tmp

end

I am going to execute this stored procedure and lets see the difference.

After executing the stored procedure in management studio, I have clicked on messages tab indicated by arrow in the above snapshot. We can see the difference, in the messages tab we have only one messages stating that “Command completed successfully”, rest all the messages has been suppressed.

Conclusion:- Setting up NOCOUNT option on has a great impact on performance, as it suppress all the messages which show the no of rows affected count in that way it reduces the network traffic.