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.

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.

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;

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)