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)
4 Comments. Leave new
Good post
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.
How do you do the same in PDW?
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