stewartc-708166 (1/28/2010)
when a new query window is opened, a connection is made to the database, which returns a result (not visible)this item is reflected in the @@rowcount as 1
Not exactly. When connection is made, no results are returned (visible or not). After establishing a connection, SSMS sends some SQL commands to the server. You can see these commands in Profiler:
SELECT SYSTEM_USER
SET ROWCOUNT 0
SET TEXTSIZE 2147483647
... a lot of other SETs ...
select @@spid
select SERVERPROPERTY('ProductLevel')
The last 'select' statement makes the @@rowcount value equal to 1.
In fact, you can see the results of these statements in the bottom of your SSMS window. For example, I see the following:
(local) (9.0 SP2) | <my username> (59) | master | 00:00:00 | 0 rows
(Oops, I'm still having SP2 on my local machine :blush:)
When I run the batch via SQLCMD, I get "0, 1" as the result.
So the answer differs for different clients. "1, 1" is the answer for SSMS, "0, 1" is the answer for SQLCMD, and there can be another answer for another client.