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.
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)
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..
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
Yes u r absolutely right……………
Interesting – this is what I get in 2008R2.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘,’.
using
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
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
Hi David,
Pretty much agree with you.
Best Regards,
Mohit Pandit
Yes, you are correct.
Yes, David you are absolutely right ! Nice explanation and fact about NOCOUNT effect in query execution.
good article and nice explanation by david.
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
Exactly, this should be the Understanding.
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
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!
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
Thanks Narendra!!!!
I will look over i think it will solve my problem.
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
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.
good its very userful
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
I found that an if statement can reset @@rowcount:. The following always returns zero, regardless of any previous selects.
IF @debug = 1 SELECT @@rowcount
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.
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
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…………
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.
your are a great Myth Buster , Pinal sir
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
“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.
Difference between on and off