SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rowcount


Rowcount

Author
Message
ziangij
ziangij
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3616 Visits: 377
thanks, i got my answer..

even after opening a new window, i got the result as 1,1 and i was wondering why ???

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
however, SET NOCOUNT ON does reset this to 0.

interesting question, I think this is going to be another interesting discussion...

Piotr ER
Piotr ER
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5359 Visits: 258
First query returns 0 ONLY when SET ROWCOUNT {something} is executed first. It doesn't matter to which value ROWCOUNT is set (2008EE).
Interesting issue but answer to the question is wrong
keyur.patel
keyur.patel
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 20
Hi

I too experienced the same result (1,1). I tried it in sql 2008

pls give the detailed explanation for the same......
ashwani24
ashwani24
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1022 Visits: 123
1,1 in that case when u have opened a new qurey window and executed that statement and 0,1 in case when u have executed a select statement, which returns nothing and then u execute the same rowcount statement. So by default 1,1 is the correct answer.
jagadeesanpv
jagadeesanpv
Mr or Mrs. 500
Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)

Group: General Forum Members
Points: 594 Visits: 224
I too got 1,1 in sql server 2008 w00t
Iggy-SQL
Iggy-SQL
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3253 Visits: 440
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! :-P


Urbis, an urban transformation company
malleswarareddy_m
malleswarareddy_m
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2763 Visits: 1189
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)
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17460 Visits: 7423
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

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Manie Verster
Manie Verster
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2321 Visits: 1030
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
vk-kirov
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4356 Visits: 4408
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 :blushSmile

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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search