SQL SERVER – How to Hide Number of Rows Affected Message? – SET NOCOUNT

This blog post is ideally titled as back to basics as it is going to talk about one of the very old topics of SQL Server. Though this may look very simple for many, this blog post conveys the message which many experts would like to revisit in their code as well. In this blog post, we are going to talk about how to hide the number of rows affected messages with the help of SET NOCOUNT.

SQL SERVER - How to Hide Number of Rows Affected Message? - SET NOCOUNT setnocount-800x302

Many of the SQL Statement when complete it returns a message suggesting how many rows are impacted via that statement. This is good information if you need to know how many rows were affected in the stored procedure or in the SQL Statement. However, if you do not need this data, it is just useless information. I have often seen users not exercising this option in their stored procedure where there is a long loop, generating lots of useless network traffic. If you do not want to know how many rows were impacted by your SQL Statement, it is a good idea to use SETNOCOUNT ON and turn off the message.

In simple words, if you do not need to know how many rows are affected, SET NOCOUNT ON as it will reduce network traffic leading to better performance. 

Here is the example, you can run once with SET NOCOUNT ON and once with SET NOCOUNT OFF and you can see how the result set differs.

Test 1: SET NOCOUNT OFF

SET NOCOUNT OFF
-- Demo Code
CREATE TABLE #Temp1 (ID BIGINT)
DECLARE @Var BIGINT
SET @Var = 1000
WHILE(@Var > 0)
BEGIN
INSERT INTO #Temp1 (ID)
VALUES (@Var)
SET @Var = @Var -1
END
DROP TABLE #Temp1;

You will see in the message windows repeated information about rows affected. As the loop is running, this value may not be useful most of the time.

SQL SERVER - How to Hide Number of Rows Affected Message? - SET NOCOUNT setnocount1

Test 2: SET NOCOUNT ON

SET NOCOUNT ON
-- Demo Code
CREATE TABLE #Temp1 (ID BIGINT)
DECLARE @Var BIGINT
SET @Var = 1000
WHILE(@Var > 0)
BEGIN
INSERT INTO #Temp1 (ID)
VALUES (@Var)
SET @Var = @Var -1
END
DROP TABLE #Temp1;

SQL SERVER - How to Hide Number of Rows Affected Message? - SET NOCOUNT setnocount2

You will see in the message windows that only one message of command completion is displayed. This will greatly reduce network traffic.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – 2016 – Wait Stats and Queues Script Updated – Identify Resource Bottlenecks
Next Post
SQL SERVER – Set AUTO_CLOSE Database Option to OFF for Better Performance

Related Posts

4 Comments. Leave new

  • Kunal Chowdhury
    October 9, 2016 1:09 am

    Good post

    Reply
  • Hi Pinal,
    Great Post. After reading your post, I am considering to modify all of my stored procedures with SET NOCOUNT ON option. If any disadvantage of this, please please let me know.

    Reply
  • How do you do the same in PDW?

    Reply
  • Divyesh Chapaneri
    March 15, 2019 4:08 am

    Hello,

    Thanks for showing how nocount is useful.

    Can you show how can I hide output text for below stored procedure ?

    EXECUTE sp_updatestats;

    Thanks,
    Divyesh

    Reply

Leave a Reply

Menu