SQL Server: SET NOCOUNT ON Statement with Examples

SET NOCOUNT ON is a set statement which prevents the message which shows the number of rows affected by T-SQL query statements. This is used within stored procedures and triggers to avoid showing the affected rows message. Using SET NOCOUNT ON within a stored procedure can improve the performance of the stored procedure by a significant margin.

SET NOCOUNT ON: This prevents the message from showing which contains the number of affected rows.
SET NOCOUNT OFF: This shows the number of affected rows in a message window.

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.

SET NOCOUNT ON/OFF with an Example

SET NOCOUNT ON
SELECT TOP 10 * 
FROM   sys.procedures;

SET NOCOUNT OFF
SELECT TOP 10 * 
FROM   sys.procedures;

Using SET NOCOUNT ON in the first T-SQL statements prevents showing the number of affected rows message.

SET NOCOUNT with @@ROWCOUNT Function

This does not have any impact on the @@Rowcount function which shows the number of affected rows within a Batch.

SET NOCOUNT ON
Declare @Temp Table
(
Number INT 
);

INSERT INTO @Temp Values (1), (2),(3), (4);

SELECT @@ROWCOUNT as NoOfRowsAffected;
SET NOCOUNT OFF

Using the @@ROWCOUNT function within SET NOCOUNT ON does not reset the @@ROWCOUNT value. The NoOfRowsAffected column is showing four rows were affected by the previous INSERT statement.

Using SET NOCOUNT ON/OFF within a Stored Procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE ABC_INSERT_DATA
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;


        Declare @Temp Table
(
Number INT 
);
INSERT INTO @Temp Values (1), (2),(3), (4);
SELECT * FROM @Temp;
END
GO

Executing the Stored Procedure Above

EXEC ABC_INSERT_DATA

You can see in the screenshot above that the executed stored procedure does not show the number of affected rows message. Using SET NOCOUNT ON within stored procedures is recommended as best practice for performance tuning in SQL Server.

You can browse our SQL Server archive articles for more useful information.