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)
