What is SET NOCOUNT ON in SQL Server?

SET NOCOUNT ON is mostly used in stored procedure to stop the message that shows the number of rows affected by the SQL statement.

Stored Procedure contains several select, alter and update statements. The result pan shows the number of rows affected by the SQL statement written in the stored procedure. By using SET NOCOUNT ON network traffic can be greatly reduced. The overall performance of the database must gradually increase.

SET NOCOUNT ON
GO
If object_id('TestNoCount') is not null
Drop procedure TestNoCount
Go
Create procedure TestNoCount 
(@number1 int, @number2 int)
As 
Begin
Declare @sum int,@Difference int,@product int
set @sum=@number1 +@number2
select @sum Sum
set  @Difference=@number1-@number2 
select @Difference Difference
set @product=@number1*@number2
select @product Product
End

--Exec TestNoCount 8,4
==========================================
Messages
Command(s) completed successfully.
==========================================

SET NOCOUNT OFF
GO
If object_id('TestNoCount') is not null
Drop procedure TestNoCount
Go
Create procedure TestNoCount 
(@number1 int, @number2 int)
As 
Begin
Declare @sum int,@Difference int,@product int
set @sum=@number1 +@number2
select @sum Sum
set  @Difference=@number1-@number2 
select @Difference Difference
set @product=@number1*@number2
select @product Product
End

--Exec TestNoCount 8,4
==========================================
Messages
(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
==========================================