During a recent presentation on SQL Server, I encountered an interesting situation that shed light on a common misconception regarding the usage of SET NOCOUNT ON and @@ROWCOUNT. In this blog post, we will explore this misconception and clarify its implications.

While demonstrating a query in SQL Server Management Studio, I noticed that the message “8 row(s) affected” and “2 row(s) affected” were being displayed. Curious about this, I decided to showcase the usage of the @@ROWCOUNT function. In my haste, I quickly wrote a simplified version of the query on the screen:

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

To my surprise, several hands went up in the audience before I even executed the script. Perplexed, I asked one person what was wrong, and they pointed out that I would not get the correct results in @@ROWCOUNT because I was using it immediately before SET NOCOUNT ON. According to them, any statement executed right before @@ROWCOUNT resets its value.

Let’s pause for a moment and reflect on this. Did you also believe the same misconception? If so, I encourage you to run the above code and observe the results. If you were surprised, like I was, then we have both learned something new.

It is important to clarify that the myth surrounding SET NOCOUNT ON and @@ROWCOUNT is just that – a myth. The usage of SET NOCOUNT ON does not affect the value of @@ROWCOUNT. In fact, SET NOCOUNT ON simply prevents the “X row(s) affected” message from being displayed, which can be useful in scenarios where the number of affected rows is not relevant.

Now that we have debunked this common misconception, I invite you to share any other SQL Server myths that you have come across. If you provide an interesting myth in the comments section, I may feature it in a future blog post and give you credit. Additionally, you will have a chance to win a copy of my new book, “SQL Server Interview Questions and Answers.”

Remember, it is always important to question and verify assumptions in the world of SQL Server. Happy querying!