SQL SERVER – Effect of SET NOCOUNT on @@ROWCOUNT

Today I had 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 hurry, I quickly wrote very similar to following example on my screen. (The real query is much complected, but for the purpose of this post I am simplifing 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 audience. This was 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 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 same way as well?

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

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 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 (http://blog.SQLAuthority.com)

About these ads

30 thoughts on “SQL SERVER – Effect of SET NOCOUNT on @@ROWCOUNT

  1. 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..

    • 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

  2. 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
    
    • 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

  3. 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

    http://davidbridge.wordpress.com/

  4. 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

  5. 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

  6. 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!

  7. 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!!

    • 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

    • 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

  8. 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.

  9. 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

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

    IF @debug = 1 SELECT @@rowcount

  11. 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.

  12. 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

  13. Pingback: SQL SERVER – Weekly Series – Memory Lane – #006 « SQL Server Journey with SQL Authority

  14. 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…………

  15. 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.

  16. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s