What is SET NOCOUNT ON in SQL Server?


By using SET NOCOUNT ON in Sql Server Stored Procedure the number of rows affected by the Sql Statements will not be returned. It will increase the overall performance of the database. By giving command SET NOCOUNT OFF the number of affected rows will again returned.

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.
==========================================


By giving command SET NOCOUNT OFF the number of affected rows will be returned.


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)
==========================================


Comments

Author: arul R22 Jun 2012 Member Level: Silver   Points : 0

Simple and sweet

Author: Rakesh Chaubey20 May 2013 Member Level: Gold   Points : 0

Perfect

Guest Author: Quiana27 May 2013

What a pleasure to find someone who thinks through the issues

Author: Phagu Mahato10 Nov 2013 Member Level: Gold   Points : 1

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, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

This statement will use inside the stored procedures.
The statement will execute for each and every statement in the stored procedure.

Syntax.

SET NOCOUNT {ON / OFF}
Set No count off

Set No count off

SET NOCOUNT { ON | OFF }


When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) 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 eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. When using the utilities provided with Microsoft® SQL Server to execute queries, the results prevent "no rows affected" from being displayed at the end Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE.

For stored procedures that contain several statements that do not return much actual data, this can provide a significant performance boost because network traffic is greatly reduced.

The setting of SET NOCOUNT is set at execute or run time and not at parse time.
Permissions

SET NOCOUNT permissions default to all users.


If the SET NOCOUNT is in “OFF” state then,

This will update the @Rowcount values for all the updates done in sp and return the DONE_IN_PROC message to the client. This will make more network traffic. Basically the DONE_IN_PROC message is only for the information. Inside the stored procedure this message popup to the client is mostly waste. So can we prevent this message by using the SET NOCOUNT ON.

When SET NOCOUNT is in “OFF” For each updates the DONE_IN_PROC message will popup. This will make more network traffic.

Author: maruthanayakam21 Feb 2014 Member Level: Silver   Points : 4

Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.
Syntax
SET NOCOUNT { ON | OFF }
Remarks
When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) 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 eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. When using the utilities provided with Microsoft® SQL Server™ to execute queries, the results prevent "nn rows affected" from being displayed at the end Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE.
For stored procedures that contain several statements that do not return much actual data, this can provide a significant performance boost because network traffic is greatly reduced.
The setting of SET NOCOUNT is set at execute or run time and not at parse time.
Permissions
SET NOCOUNT permissions default to all users.
Examples
This example (when executed in the osql utility or SQL Query Analyzer) prevents the message (about the number of rows affected) from being displayed.
USE pubs
GO
-- Display the count message.
SELECT au_lname
FROM authors
GO
USE pubs
GO
-- SET NOCOUNT to ON and no longer display the count message.
SET NOCOUNT ON
GO
SELECT au_lname
FROM authors
GO
-- Reset SET NOCOUNT to OFF.
SET NOCOUNT OFF
GO


Regards,
Marudhu...



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: