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.

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)

SQL Function, SQL Scripts, SQL Server
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

42 Comments. Leave new

  • I think the code should be :
    DECLARE @table TABLE
    (
    col1 INT IDENTITY(1, 1) ,
    col2 INT
    )
    INSERT INTO @table
    ( col2 )
    VALUES ( 2932 ),
    ( 142 ),
    ( 352 ),
    ( 342 )
    SELECT @@rowcount
    SET NOCOUNT ON
    SELECT @@rowcount
    SELECT *
    FROM @table
    SELECT @@rowcount
    SET NOCOUNT OFF..
    in order to test this myth..

    Thank you sharing even I was not aware of this myth..

    Reply
    • Kindly concentrate about what is Pinal wanted to emphasis… don’t mind; you can find out order here.

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

      Reply
    • bhasker reddy
      August 31, 2012 11:21 am

      Yes u r absolutely right……………

      Reply
  • Interesting – this is what I get in 2008R2.

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘,’.

    using

    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
    
    Reply
    • I too got the error in 2008R2…

      then I used below script to get the O/P…

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

      Reply
  • In your example @@ROWCOUNT is being read immediately AFTER the SELECT statement
    It is true that issuing a statement between the select and the @@rowcount would cause the row count to be reset to 0 and in your text above it reads a little like you are trying to make this point, however, I don’t think you your example makes this very clear.

    e.g.

    SELECT * FROM sys.master_files
    SET NOCOUNT ON
    SELECT @@rowcount

    Will return a zero result but it has nothing to do with the effect of SET ROWCOUNT ON, it is purely because there is a statement between the DML and the @@Rowcount querry (it could have been almost any not result statement).

    The point I think you are trying to make here is that the NOCOUNT statement only affects the information messages and not the DML statement results hence, in the following example, SELECT @@Rowcount returns a value regardless of the setting of SET NOCOUNT, however no count message is sent to the messages window.

    SET NOCOUNT ON
    SELECT * FROM sys.master_files
    SELECT @@rowcount

    David Bridge

    Reply
  • good article and nice explanation by david.

    Reply
  • Pinal Sir,
    I think that if we use Insert, Update or delete statements just before @@rowcount, it gives number of rows affected by the just previous statement. When we use select statement just before @@rowcount, it gives number of rows retrieved by select statement.
    Set NOCOUNT statement stops the “rows affected” messages coming on message screen. It will have no effect on @@rocount functionality.

    Thanks

    Reply
  • I think below script will help to test

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

    Reply
  • Nakul Vachhrajani
    December 4, 2011 7:36 pm

    I have been working with SQL Server for about 7 years now, and I will confess that in my first year, I too believed in this myth. Then, I had to develop a code that used the count returned by @@ROWCOUNT and that busted the myth for me.

    Thank-you for sharing your experience, and also in ensuring that one more myth now lies busted!

    Reply
  • Vinay B Hiraskar
    December 5, 2011 7:04 pm

    Hi Pinal Sir,
    I am having a table called Test consists of Two column Name and Key Fields;
    (Pramod,1111),(Sushil 1234),(Ashish,2345),(Prakash,4563),(Anil,1111), (Akash,4563) names and Key are followed Respectively.

    Is There any query which will group a common value field member and results as follows when i Execute a’ Select name, key from Test’ Statement:
    Pramod,Anil(Since it’s Value Member are Same)
    Sushil
    Ashish
    Prakash,Akash(Since it’s Value Member are Same)

    Thank in Advance Sir!!

    Reply
    • Hi Vinay,

      i have one solution for your problem, but it is some what lengthy process i feel . i always remembering one proverb “Something is better than Nothing”.
      Based on that proverb, i developed the following code. Please see this, it may helps to you.

      –Copy Of the Original TestSeries Table For Distinct [KEY] values
      CREATE TABLE #TestSeries ([KEY] INT)
      INSERT INTO #TestSeries
      SELECT DISTINCT [KEY] FROM TestSeries

      — Table For Results
      CREATE TABLE #TestSeriesResult (NAME VARCHAR(250),[KEY] INT)

      –Declaring Parameters
      DECLARE @RowCount INT,
      @RowStart INT,
      @listStr VARCHAR(20),
      @Value INT

      SET @RowStart = 1
      SELECT @RowCount = COUNT(DISTINCT [KEY]) FROM #TestSeries

      WHILE (@RowStart <= @RowCount)
      BEGIN
      SET @Value = (SELECT TOP 1 [KEY] FROM #TestSeries)
      SELECT @listStr = COALESCE(@listStr+',' ,'') + Name FROM TestSeries WHERE [KEY] = @Value
      INSERT INTO #TestSeriesResult (NAME,[KEY])
      SELECT @listStr,@Value
      DELETE FROM #TestSeries WHERE [KEY] = @Value
      SET @RowStart = @RowStart + 1
      SET @listStr = ''
      END
      SELECT CASE WHEN (CHARINDEX(',',NAME,0) ) = 1
      THEN SUBSTRING(NAME,CHARINDEX(',',NAME,0)+1,LEN(NAME))
      ELSE NAME
      END AS NAME,
      [KEY]
      FROM #TestSeriesResult

      Reply
    • Santosh Kumar Biswal
      November 5, 2013 9:19 pm

      Hi Vinay,

      You could try the following code for your requirement.

      DECLARE @table TABLE(col1 INT IDENTITY(1,1), ID INT, Name Varchar(50))
      INSERT INTO @table(Name,ID)
      VALUES (‘Pramod’,1111)
      INSERT INTO @table(Name,ID)
      VALUES (‘Sushil’, 1234)
      INSERT INTO @table(Name,ID)
      VALUES (‘Ashish’,2345)
      INSERT INTO @table(Name,ID)
      VALUES (‘Prakash’,4563)
      INSERT INTO @table(Name,ID)
      VALUES (‘Anil’,1111)
      INSERT INTO @table(Name,ID)
      VALUES (‘Akash’,4563)

      SELECT Id,STUFF(
      (SELECT ‘,’+T.Name
      FROM @table T
      WHERE T.Id=A.ID
      FOR XML PATH(”)),1,1,”)

      FROM @table A
      GROUP BY A.ID

      Reply
  • Hi Vinay,
    i have one more solution for you, which i think very easy to implement.
    Please see this, it may helps to you.

    SET NOCOUNT ON
    DECLARE @table TABLE(col1 INT IDENTITY(1,1), ID INT, Name Varchar(50))
    INSERT INTO @table(Name,ID)
    VALUES (‘Pramod’,1111),(‘Sushil’, 1234),(‘Ashish’,2345),(‘Prakash’,4563),(‘Anil’,1111), (‘Akash’,4563)

    — Table For Results
    DECLARE @table2 TABLE(col1 INT IDENTITY(1,1), ID INT, Name Varchar(150))

    — Variable Declaration
    Declare @Cnt As Int=0
    Declare @RowID As Int=0
    Declare @RowName As Varchar(50)=NULL

    Declare @RowFirst As Int=1
    Declare @RowCount As Int=0
    SELECT Top 1 @RowCount=Col1 FROM @table Order By col1 Desc

    While (@RowFirst=1)
    Begin
    Update @table2 Set Name=Name+’, ‘+@RowName Where ID=@RowID
    End
    Else
    Begin
    Insert Into @table2(ID,Name) Values(@RowID,@RowName)
    End
    End

    Set @RowFirst=@RowFirst+1
    End
    Set NOCOunt OFF

    Select * From @table2

    Thanks.

    Reply
  • i am a beginner in ms sql… can u pls explian the concept of nocount on,,,if possible can u simplify your answer..

    Reply
  • good its very userful

    Reply
  • check the below code i am setting nocount as off still getting rowcount as 4

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

    Reply
  • I found that an if statement can reset @@rowcount:. The following always returns zero, regardless of any previous selects.

    IF @debug = 1 SELECT @@rowcount

    Reply
  • Don Schaeffer: The point is that ANY statement, anything at all, always resets @@ROWCOUNT. If you need the result of @@ROWCOUNT you *must* use it *immediately* after the query you need to count. If you need to do any conditional logic, you must assign @@ROWCOUNT to another variable.

    The same is true of @@ERROR, which is why TRY … CATCH is such a useful addition.

    Nighil: Yes, because the script queries the @@ROWCOUNT variable immediately after the ‘SELECT * FROM table’, it will always return 4. It doesn’t matter whether NOCOUNT is ON or OFF. NOCOUNT merely controls whether the count is *directly* returned to the client (e.g. sqlcmd or Management Studio will output ‘(4 row(s) affected.)’) or not (nothing is output).

    This information isn’t usually all that useful, it just wastes network bandwidth (and potentially could make SQL Server have to block, waiting for the client to consume the message, if the client’s network buffer is full) so it’s common to SET NOCOUNT ON. (OFF is the default.) It’s used by some frameworks to detect conflicts, for example if you want to ensure that the values of a row you’re updating haven’t changed since you read them, you might write:

    UPDATE table
    SET col2 = 4526
    WHERE col1 = 2 AND col2 = 142

    If the row hasn’t changed, the update will find one row and therefore return ‘1 row affected’. If it *has* changed, col2 will not match and it will return ‘0 rows affected’, therefore you know that there was a conflict.

    If instead the code was:

    SELECT * FROM table
    SET NOCOUNT ON — or SET NOCOUNT OFF, or PRINT ‘Hi!’
    SELECT @@ROWCOUNT

    then you’d get 0, rather than 4. The fact that you’re modifying the NOCOUNT option is a total red herring; the two are not related.

    Reply
  • mansoor ahmed
    December 6, 2012 9:50 pm

    I want to Make a Visitor Counter for my Publishers.
    for example:
    I have 150+ premium users who have 3 ads of each and everyone…
    i want to count all users ads view with trace IP and Store in one table in database….
    i can count by session but it works only on Session… if Session reset then it’s counting must be reset….
    I want to count continuously…Kindly Help Me

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

    Result :1 2932
    1

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

    Result :1 2932
    0
    In above statements, i changed SET NOCOUNT ON position then its showing different Outputs.

    Nice Example…………

    Reply
  • Sir, I have found the following error while working in ASP page.
    ADODB.Recordseterror ‘800a0e78’

    Operation is not allowed when the object is closed.

    But when I have added SET NOCOUNT ON it is sorted out.
    How this is linked in. Kindly clarify my doubt.

    Reply
  • Arvind singh Chandel
    July 18, 2014 12:44 pm

    your are a great Myth Buster , Pinal sir

    Reply
  • Suppose I have a table
    DECLARE @table TABLE(col1 INT IDENTITY(1,1), col2 INT)
    INSERT INTO @table(col2) VALUES (2932),(142),(352),(342)
    SELECT * FROM @table

    I want to update the value of col2 to 1000 for col1=5
    UPDATE @table SET col2=1000 where col1=5
    IF @@ROWCOUNT=0
    INSERT INTO @table(col2) VALUES (1000)

    Suppose the table is used by many users at a time.
    Should I use @@ROWCOUNT or use if exists(select *..) to check if col1=5 exists.
    Please reply

    Reply
  • “When I asked the reason they told me that because any statement when ran right before @@ROWCOUNT resets its value.”

    That statement is correct, but your example is misleading and incorrect. I believe that you’re trying to show that @@ROWCOUNT is not reset due to the SET NOCOUNT ON, which is not true. You’re @@ROWCOUNT is returning 4 not due to the insert statement, but due to the SELECT STATEMENT. After you SET NOCOUNT ON, it is setting @@ROWCOUNT to 0 from 4, however, it is being set back to 4 by your SELECT statement.

    Reply

Leave a ReplyCancel reply

Exit mobile version