SQL SERVER – Effect of SET NOCOUNT on @@ROWCOUNT

Today I had a very interesting experience when I was presenting on SQL Server. While I was presenting the session when I ran query SQL Server Management Studio returned message like (8 row(s) affected) and (2 row(s) affected) etc. After a while at one point, I started to prove usage of @@ROWCOUNT function.

As I was in a hurry, I quickly wrote very similar to the following example on my screen. (The real query is much complected, but for the purpose of this post I am simplifying it here.

DECLARE @table TABLE(col1 INT IDENTITY(1,1), col2 INT)
INSERT INTO @table(col2) VALUES (2932),(142),(352),(342)
SET NOCOUNT ON
SELECT * FROM @table
SELECT @@rowcount
SET NOCOUNT OFF

Before I execute the script nearly 10 hands went up in the audience. This was a bit surprise to me as I do not know why they all got alerted. I assumed that there should be something wrong with either project, screen or my display. I quickly asked to one person what is wrong, he pointed out that I will not get the right results in @@ROWCOUNT as I am using immediately before SET NOCOUNT ON. When I asked the reason they told me that because any statement when ran right before @@ROWCOUNT resets its value.

Solarwinds

Let us stop here for a second – do YOU think the same way as well?

If yes, run above code and check your result in following image.

SQL SERVER - Effect of SET NOCOUNT on @@ROWCOUNT rowcountwithnocount

If you believed the same I guess we learned something new. I have no problem confessing I learn something new every single time, I learn something new. However, I was a bit surprised with this common myth of SET NOCOUNT ON and @@ROWCOUNT.

Are there any other common myths – if yes, do share here with your comment. If I think it is interesting, I will publish it this blog with due credit and you can win my new SQL Server Interview Questions and Answers book as well.

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

Solarwinds
, ,
Previous Post
SQL SERVER – Where Can YOU Get My Books – SQL Server Interview Question and Answers
Next Post
SQLAuthority News – Download Whitepaper 5 Tips for a Smooth SSIS Upgrade to SQL Server 2012

Related Posts

41 Comments. Leave new

  • Kamalesh kumar
    January 3, 2016 8:04 am

    Difference between on and off

    Reply
  • vinod gorantla
    March 3, 2016 11:50 am

    good artical boos, it is very useful for us

    Reply
  • quick note on this. It seems to affect .NET code. My guess is ADO.NET uses the ‘rows affected’ message to determine how many rows were affected and returns that number with ExecuteNonQuery. If you set nocount on it this method will return -1 and you will need another way to determine the number of records affected (example ExecuteScalar with select @@rowcount). Sorry for the quick reply, didn’t google around to check my findings…

    Reply
  • Stefano Masseroli
    March 9, 2017 5:02 pm

    Just look to the official documentation: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql?view=sql-server-2017 … The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

    Reply
  • Hi, I am facing similar problem here when I use select query it return 10 rows but when I use that select query to insert record int he table it return 0 rows. Please guest.. Here is the sample code :-

    begin
    begin try

    select * from table1;

    select @@ROWCOUNT; — here I am getting 10 rows as count

    insert into table2(col1,col2,col3)
    select col1,col2,col3 from table1;

    select @@ROWCOUNT; ———— here I am getting 0 rows as count ( but after this sp executed. it has inserted 10 rows in table2 ) .

    end try
    BEGIN
    CATCH
    THROW;
    END CATCH
    end

    Reply
  • Hi. Any command resets @@ROWCOUNT

    I leave here your example:

    DECLARE @table TABLE(col1 INT IDENTITY(1,1), col2 INT)
    INSERT INTO @table(col2) VALUES (2932),(142),(352),(342)

    SET NOCOUNT ON
    SELECT @@rowcount [Insert]

    SELECT * FROM @table WHERE col1 > 2
    SELECT @@rowcount [Select]

    SET NOCOUNT OFF

    Reply
    • The NOCOUNT doesn’t matter. Is about just any command.

      DECLARE @table TABLE(col1 INT IDENTITY(1,1), col2 INT)
      INSERT INTO @table(col2) VALUES (2932),(142),(352),(342)

      IF 1=1
      SELECT @@rowcount [Insert]

      SELECT * FROM @table WHERE col1 > 2
      SELECT @@rowcount [Select]

      Reply
  • Naga Chandraiah Nalamaru
    June 10, 2018 2:44 pm

    gud article

    Reply

Leave a Reply

Menu