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
good artical boos, it is very useful for us
Thanks Vinod !
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…
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.
True, but regardless of the documentation I tested it in .NET and ExecuteNonQuery relies on it and doesn’t return the correct value if you set this off. There’s some pointers to my findings if you search stackexchange. For example: https://stackoverflow.com/questions/29679780/executenonquery-and-set-nocount-on
Chrz
Thanks Thierry.
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
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
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]
gud article
If you set nocount on in a trigger, the @@Rowcount will be zero.
alter trigger utr_testNocount on ordln
after insert,update
as
set nocount on
Update ordln
set TransGr =@@ROWCOUNT
from inserted
where inserted.OrdNo = ordln.ordno and inserted.LnNo = ordln.LnNo