Journey to SQL Authority with Pinal Dave - SQL SERVER – Effect of SET NO COUNT on @@ROWCOUNT

SQL SERVER – Effect of SET NO COUNT on @@ROWCOUNT

Posted: 01 Dec 2011 05:30 PM PST

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 @tableSELECT @@rowcountSET 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)