Rowcount

Question

Rowcount

January 29, 2010 at 12:04 am

This was removed by the editor as SPAM

Viewing 15 posts - 16 through 30 (of 132 total)

You must be logged in to reply to this topic. Login to reply

Iggy-SQL SSCarpal Tunnel Points: 4169 More actions · Answer 1

The posts saying if you set SET NOCOUNT ON or SET ROWCOUNT <any value> first is correct, you'll get 0,1 as the answer. unfortunately the question didn't mention anything about it, so 1,1 is what most people would have answered (as shown by the percentage of the chosen answer).

I got it wrong (chose 1,1) but I don't really care, at least I learn something new! 😛

Urbis, an urban transformation company

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 2

Dear all,

SET NOCOUNT ON

Does not reset the @@rowcount to 0.

@@rowcount is zero when we execute the system Defaults like as shown

set ansi_padding on

select @@rowcount

set nocount on

select @@rowcount

set ansi_nulls on

select @@rowcount

set ansi_warnings off .... so on

for all system defaults like above when we set system defaults on/off @@rowcount will be displayed as zero.

other wise by defalut @@rowcount is zero.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Manie Verster SSCertifiable Points: 7152 More actions · Answer 3

Can I please ask that questions asked please take all necessary conditions in consideration. I lost a point and so what but when a question is asked and things like nocount and rowcount is omitted then please first make sure that your answer is what it really is.:hehe:

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

vk-kirov SSCertifiable Points: 7686 More actions · Answer 4

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.

darren.sunderland Mr or Mrs. 500 Points: 531 More actions · Answer 5

In SQL 2000 I get 0,1 for the first run in a new window, then 1,1 for all subsequent runs. In SQL 2005 I get 1,1 for all runs

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

stewartc-708166 (1/29/2010)
upon opening a new query window in ssms, try
set nocount on
go
select @@ROWCOUNT
select @@ROWCOUNT
then the result will be 0,1
otherwise it will be 1,1

This is correct, and thus makes the answer for the question correct.

If one omits the set nocount on statement, then the results would be 1,1.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

malleswarareddy_m (1/28/2010)
Dear all,
SET NOCOUNT ON
Does not reset the @@rowcount to 0.
@@rowcount is zero when we execute the system Defaults like as shown
set ansi_padding on
select @@rowcount
set nocount on
select @@rowcount
set ansi_nulls on
select @@rowcount
set ansi_warnings off .... so on
for all system defaults like above when we set system defaults on/off @@rowcount will be displayed as zero.
other wise by defalut @@rowcount is zero.

Valid point.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

vk-kirov SSCertifiable Points: 7686 More actions · Answer 8

darren.sunderland (1/29/2010)
In SQL 2000 I get 0,1 for the first run in a new window

Did you use Query Analyzer or SQL Server Management Studio?

honza.mf SSCertifiable Points: 5519 More actions · Answer 9

Just another QotD with correct answer that is correct only in some settings.

I wrote only one question (I am lazy), but I tried the script on more versions of SQL Server. The discussion was very short that time. I missed more settings as ANSI NULLS on/off, if I will write some another I will try this too.

darren.sunderland Mr or Mrs. 500 Points: 531 More actions · Answer 10

darren.sunderland

Mr or Mrs. 500

Points: 531

January 29, 2010 at 1:23 am

#1110527

I used query analyzer

honza.mf SSCertifiable Points: 5519 More actions · Answer 11

And what about the answer "Any number, 1" or better "Nonnegative number, 1".

This is correct in all situations and after any scripts run in the past by the same connection.

johan.van.den.bosch Default port Points: 1483 More actions · Answer 12

johan.van.den.bosch

Default port

Points: 1483

January 29, 2010 at 1:33 am

#1110531

I checked on sql server 2005 and got as result 1, 1

JayK SSCrazy Points: 2679 More actions · Answer 13

I ran this query in a new query window on SQL 2008 and got 1,1.

pauljones Old Hand Points: 372 More actions · Answer 14

Tried this on SQL Server 2005 and the result was 1,1