Rowcount

Question

Post reply

Rowcount

Viewing 15 posts - 91 through 105 (of 132 total)

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

Tom Garth SSCertifiable Points: 6193 More actions · Answer 1

@@ROWCOUNT always returns the number of rows affected by the most recent command on the current connection.

Some commands might surprise you. The following returns 1, 1.

declare @ChangeRowCount bit

set @ChangeRowCount = 1

select @@ROWCOUNT

...where this returns 0, 1.

declare @ChangeRowCount bit

set @ChangeRowCount = 1

print @ChangeRowCount

select @@ROWCOUNT

Neither of these statements are affected by SQL Server version, or the query tool used.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Patrick Cahill SSCertifiable Points: 6483 More actions · Answer 2

I also got (1,1). It looks like some of these questions need to be tested a little more.

tmacs33 SSC Eights! Points: 975 More actions · Answer 3

I assumed the answer would be (0,1) but when I ran it I got (1,1) on many version.

I'm just posting to get my "point" back.

Interesting debate though.

sjimmo SSChampion Points: 11139 More actions · Answer 4

I have tried this code on both SS2K5 and 2K. Both are default installations.

On 2K I got the 0,1 and on 2K5 I got 1,1.

The question does not specify version, nor does it call for setting NOCOUNT to ON.

The way the question is worded, I would actually expect 1,1 as the successful select in 2K5 does inheritently return a 1 which @@ROWCOUNT evidently translates into a row.

This question does not specify a version of SQL. I would agree that it is wrong.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

Longy SSCommitted Points: 1613 More actions · Answer 5

Ha, I am assuming the large majority of people who answered 1,1 ran this through first as very few people would have known this. It's a kind of karma for cheating.

crashdan SSCommitted Points: 1990 More actions · Answer 6

well i thought about it and then ran it to confirm my theory (my theory was 0,1) and the result said otherwise

Chad Crawford SSChampion Points: 11610 More actions · Answer 7

Could the difference between 0,1 and 1,1 be due to the settings in the particular client you are using to connect? As one poster noticed, SSMS sends a whole sequence of queries to SQL Server before giving you the empty window, some of which return result sets. I noticed that changing the options I have set in SSMS change the results from the initial SELECT @@Rowcount.

So, in defense of this QOD: If you executed the queries in the QOD and got 1,1 – you executed other queries (even though you didn't realize it) before executing the select @@ROWCOUNT and that is why 1,1 was returned.

And (to be fair), on the flip side, the question should have assumed that most people would use SSMS and been clear about the settings used, or indicated that a "raw connection" (or some other term for a “clean” connection) was the basis.

But, in defense of the QOD again: They are very, very hard to write. I can see how someone would write a question like this and think that they had all the bases covered for different versions, editions, and configurations - they used the same settings from their client on each one, so of course it would return the same and never raise a flag. I don't know, nor do I think about, the settings, memory, configuration and environment that my coworker has set up when I send them a query to run - I assume that it will be much like mine and if they get different results, then I dig into it. The SSMS options isn't something that I often (ok, I admit it *ever*) consider when designing a query for someone.

This is an excellent question about @@ROWCOUNT that reminds you that @@ROWCOUNT does indeed return a rowcount itself and you need to be very careful when coding to use it. It is easy to update a procedure and stick some seemingly innocent code right before the @@ROWCOUNT and not realize that it is going to change the @@ROWCOUNT value. It's the kind of bug that can be difficult to find too, since sometimes, @@ROWCOUNT would still return what you wanted, just not for the reason you expected.

Amit - Thanks for the question.

Chad

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Has the question been changed? I just answered it and it specified SQL Server 2005/2008, in a just opened connection (no other commands run).

I was surprised that it would return:

1

when run.

I ask the above, because from the comments it appears that it may have been updated.

Simon Liddle SSCrazy Points: 2580 More actions · Answer 9

The question HAS been changed. As well as that, my answer (which was 0,1 previously) is now showing as 1,1 (and is still marked correct)! :w00t:

crashdan SSCommitted Points: 1990 More actions · Answer 10

i believe it has been corrected to include 1,1 as a correct answer

sjimmo SSChampion Points: 11139 More actions · Answer 11

Chad - You got me thinking.

I opened an OSQL session in DOS to a SS2K5 box, and running the code as is got the 0,1 answer.

BUT - how many people would do that? How many answered based upon this vs using SSMS?

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

Simon Liddle SSCrazy Points: 2580 More actions · Answer 12

Not related to the debate about the quality of QotD, but if I try running SELECT @@ROWCOUNT using osql against SQL2000 it returns my SPID! Any idea why?

(It works as expected against 2005, not tested 2008.)

Simon Liddle SSCrazy Points: 2580 More actions · Answer 13

sjimmo (1/29/2010)
Chad - You got me thinking.
I opened an OSQL session in DOS to a SS2K5 box, and running the code as is got the 0,1 answer.
BUT - how many people would do that? How many answered based upon this vs using SSMS?

I answered on the assumption that when I connected to the server, it would be a completely "fresh" connection - never really occurred to me that the client tools of course need to gather some info and set some settings before letting you loose in the query window. The question (and following debate) got me thinking about it, hence me profiling both SSMS and QA and learning something new about how each of them works. 🙂

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 14

The question has been changed. It was edited to say SS2K5 and the answer moved to 1,1. All points have been awarded back.

I believe I tested this, but it was months ago. Likely I tested it on SS2K and it appeared to work. I would guess the author did the same thing.

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 15

mukeshkane (1/29/2010)
I also checked it on 2005 and the result is 1,1
how it can be 0, 1.
Can somebody explain?

I think I can. I chose the 0, 1 option it got it wrong, which made me curious to find out why and I decided to check it out. I opened SSMS, hit Ctrl+N to open new file and pasted the statements there. I understand that if I just ran the query I would get 1,1 results but I would never run any query without first getting out of the default database (which happens to be master), and so I opted to select some other database first from the database dropdown on the top left of SSMS toolbar. Executing query then gave me 0, 1.

To summarize:

Connect SSMS, open new window, type select @@rowcount; select @@rowcount; This will give 1,1 results

Connect SSMS, open new window, chose the database of your choice from the dropdown, type select @@rowcount; select @@rowcount; This will give 0,1 results

I think that the correct answer should be some number >= 0, 1 rather than hard 1, 1. Other people already pointed it out and I totally agree. Usually when someone posts a bad question with a questionable answer like this, it generates a great deal of discussions and ends up to be a good question just because of it.

Oleg