Have you ever noticed SET NOCOUNT ON statement in T-SQL statements or stored procedures in SQL Server? I have seen developers not using this set statement due to not knowing it.

In this article, we will explore why it is a good practice to use SET NOCOUNT ON with T-SQL statements. We will also learn the performance benefit you can get from it.

Introduction

Before we explore this statement, let’s create a sample table in the SQL Server database with the following script.

Let’s insert a few records data in this table using the following script.

Once you execute the command, you get the following message in SSMS. You get ‘1-row affected’ message for each row.

Suppose you insert a million rows in the table and for each record, you get this message. As you can see, it is not a useful message and does not provide relevant information.

Now, let’s select the records from this table.

In the output, we get two tabs Results and Messages.

• Result tab shows the record from the table
• The messages tab gives the number of rows affected message

Let’s create a stored procedure to get the records from the specified table.

Execute this stored procedure, and you get a similar output in the following screenshot as well.

SET NOCOUNT ON/OFF statement controls the behavior in SQL Server to show the number of affected rows in the T-SQL query.

• SET NOCOUNT OFF – By default, SQL Server shows the number of affected rows in the messages pane
• SET NOCOUNT ON – We can specify this set statement at the beginning of the statement. Once we enable it, we do not get the number of affected rows in the output

Let’s try running the previous queries with NOCOUNT statement.

• Execute the insert statement after enabling the NOCOUNT

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

  TRUNCATE TABLE tblEmployeeDemo;   GO   SET NOCOUNT ON   USE [SQLShackDemo]   GO   INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (1, N'Grace', N'Female')   GO   INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (2, N'Gordon', N'Male')   GO   INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (3, N'Jaime', N'Female')   GO   INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (4, N'Ruben', N'Female')   GO   INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (5, N'Makayla', N'Male')   GO   INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (6, N'Barry', N'Female')   GO   INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (7, N'Ramon', N'Male')   GO   INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (8, N'Douglas', N'Male')   GO   INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (9, N'Julian', N'Female')   GO   INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (10, N'Sarah', N'Female')   GO

  Once we execute the above queries we do not get messages of 1 row(s) affected. It gives the following message.

  

• Execute the Select statement with NOCOUNT ON, and you get the following output

  1 2 3

  SET NOCOUNT ON   SELECT *   FROM tblEmployeeDemo;

  Similar to the insert statement, we did not get the number of rows affected message in the select statement output.

  

• Execute Stored procedure

  We cannot directly execute the stored procedure with the SET NOCOUNT ON statement. The above statement does not work on the stored procedure.

  1 2	SET NOCOUNT ON    Exec SP_tblEmployeeDemo

  We either need to create a new stored procedure or alter the procedure and add the SET NOCOUNT statement as per the following script.

  1 2 3 4 5 6 7

  ALTER PROCEDURE SP_tblEmployeeDemo   AS       BEGIN           SET NOCOUNT ON;           SELECT *           FROM tblEmployeeDemo;       END;

  Execute the stored procedure, and we get the required output without the number of affected rows message.

  1	Exec SP_tblEmployeeDemo

  

Configure the behavior of NOCOUNT at instance level

The SET NOCOUNT ON works at the session-level. We need to specify it with each session. In the stored procedures, we specify the code itself. Therefore, it does not require specifying explicitly in the session.

We can use the sp_configure configuration option to use it at the instance level. The following query sets the behavior of SET NOCOUNT ON at the instance level.

If we specify the NOCOUNT ON/OFF in the individual session, we can override the behavior configured at the instance level.

SET NOCOUNT and @@ROWCOUNT function

We can use the @@ROWCOUNT function to get the number of affected rows in SQL Server. The NOCOUNT ON function does not have any impact on the @@ROWCOUNT function.

Execute the following query, and we get the number of rows affected with the Insert statement.

SET NOCOUNT ON and the SQL Trigger

Let’s check the impact of the NOCOUNT statement on the SQL Triggers.

The following command inserts two records in the tblEmployeeDemo table.

Create another table to store the record transaction records inserted using the trigger.

Let’s create a SQL INSERT, Update trigger to capture the records for the insert, update values.

Execute the following query to update an existing value in the tblEmployeeDemo table, and it invokes the SQL trigger for inserting record in the Audit_tblEmployee table.

The update statement updates only one record; however, in the following SSMS message, it shows two rows affected.

It might create issues for us if the further code depends upon the number of rows affected message. We get this message due to update record tblEmployeeDemo and insert record in the Audit_tblEmployee table.

We do not want the result ‘ 1 row affected’ for the data insertion in the audit table. We should use the trigger with SET NOCOUNT ON for suppressing this message.

Let’s alter the trigger with the following script:

Rerun the update statement, and we get the expected result and get only the number of affected rows using the update statement.

The Performance impact of NOCOUNT statement

According to Microsoft documentation, using NOCOUNT option can provide a significant performance improvement.

Let’s explore this performance benefit with the following example.

Create two different stored procedures with different NOCOUNT properties.

• Create a stored procedure with default behavior ( NOCOUNT OFF)

  1 2 3 4 5 6 7 8 9

  CREATE PROC NOCountTest(@N INT)   AS        DECLARE @NumberofRecords INT;        SET @NumberofRecords = 0;        WHILE @NumberofRecords < @N            BEGIN                SET @NumberofRecords = @NumberofRecords + 1;            END;       GO

• Create stored procedure with explicit set statement

  1 2 3 4 5 6 7 8 9 10 11

  CREATE PROC NOCountTest_ON(@N INT)   AS    SET NOCOUNT ON        DECLARE @NumberofRecords INT;        SET @NumberofRecords = 0;        WHILE @NumberofRecords < @N            BEGIN                SET @NumberofRecords = @NumberofRecords + 1;            END;    SELECT @NumberofRecords       GO

Execute both the stored procedures with the different number of rows 1000, 10000, 100000 and 1000000. We want to capture client statistics for these executions. In the query window of SSMS, go to Edit and enable the Include Client Statistics.

Let’s compare the client statistics

• with SET NOCOUNT OFF

  

• With SET NOCOUNT ON

  

Let’s put both screenshots of client statistics together to see a difference

We can see a huge difference in the TDS packagers received from the server, Bytes received from the server and the client processing time. The number of the select statement also shows a significant improvement. We did not specify the Select statement in the stored procedure, but still, SQL Server treats SET statement as a select statement with the default NOCOUNT value. We can reduce the network bandwidth with the SET NOCOUNT ON option in the stored procedures or T-SQL statements. It might not improve the query performance drastically, but definitely, it puts an impact on the processing time, reducing the network bandwidth and client processing times.

Conclusion

In this article, we explored the behavior of T-SQL statements and stored procedures using the SET NOCOUNT ON. We should consider this SET option and eliminate unnecessary messages to reduce network traffic and improve performance.

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• Understanding PostgreSQL SUBSTRING function - September 21, 2024
• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023