Search code examples
t-sqlrowcount

Why is @@rowcount equal to 1 when run in complete isolation?

According to the Microsoft @@ROWCOUNT documentation,

Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT 'Generic Text'.

Somebody else on Stackoverflow also asked why @@rowcount equals 1 when no rows are updated. However, that question used an update statement.

My confusion starts with this example, where no other statements appear:

enter image description here

Reading further into this (Executing if @@rowcount>0 resets @@rowcount to 0. Why?), I then did a second test:

enter image description here

I'm curious to understand please why @@rowcount equals 1 when no other statements are used? Is it because the actual SELECT @@ROWCOUNT statement itself causes an update of @@rowcount?

Solution

  • When running SSMS on windows, I indeed get OP's behaviour. By both doing an inputbuffer check and by using the profiler, it seems that when you open a new query window, ssms performs the following batch:

    select @@spid;
select SERVERPROPERTY('ProductLevel');

    This results in that default ace.