Rowcount

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Amit mishra » Rowcount

134 posts, Page 2 of 14

««1 2 3 4 5 »»»

Rowcount

Rate Topic

Display Mode

Topic Options

Author

Message

ziangij

Posted Thursday, January 28, 2010 11:06 PM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 2:53 AM
Points: 1,526, Visits: 359

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

Post #855777

Piotr Ryszkowski

Posted Thursday, January 28, 2010 11:18 PM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, April 27, 2011 9:10 AM
Points: 1,814, Visits: 60

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

Post #855780

keyur.patel

Posted Thursday, January 28, 2010 11:23 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 28, 2011 7:06 AM
Points: 151, 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......

Post #855782

ashwani24

Posted Thursday, January 28, 2010 11:23 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 2:23 AM
Points: 387, Visits: 80

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.

Post #855784

jagadeesanpv

Posted Thursday, January 28, 2010 11:30 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, March 30, 2013 6:06 AM
Points: 532, Visits: 224

I too got 1,1 in sql server 2008 w00t

Post #855786

Iggy-SQL

Posted Thursday, January 28, 2010 11:31 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:02 PM
Points: 2,855, Visits: 404

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! Tongue

Urbis, an urban transformation company

Post #855788

malleswarareddy_m

Posted Thursday, January 28, 2010 11:49 PM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:21 AM
Points: 1,843, Visits: 971

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)

Post #855791

Stewart "Arturius" Campbell

Posted Friday, January 29, 2010 12:04 AM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 1:49 AM
Points: 3,123, Visits: 4,310

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”

Post #855800

Manie Verster

Posted Friday, January 29, 2010 12:47 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:01 AM
Points: 1,151, Visits: 878

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

Manie Verster
Developer
Johannesburg
South Africa

Life is about choices.... I choose to be happy today

Post #855827

vk-kirov

Posted Friday, January 29, 2010 1:00 AM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 1:32 AM
Points: 3,187, Visits: 4,140

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.

Post #855836

« Prev Topic | Next Topic »

134 posts, Page 2 of 14

««1 2 3 4 5 »»»

Permissions