SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Jason S Wong / Transaction rowcount / Latest Posts

RE: Transaction rowcount

L' Eomot Inversé — Sat, 16 Oct 2010 12:57:25 GMT

nice tidy question with no tricks. clear explanation.

RE: Transaction rowcount

EugeneMoulin — Thu, 05 Aug 2010 10:15:45 GMT

I agree Fozzie !

RE: Transaction rowcount

Fozzie — Thu, 05 Aug 2010 09:57:57 GMT

Oleg,Thanks for the heads up and clear explanation, does make a lot of sense. Will probably remember that one for a long time. :-)Eugene, Whilst it's not neccessary to have a space between the code and the comment, it's certainly nicer on the eye. The code highlighter on the forum makes it look wrong as it only identifies whole words. Generally I'd have the spaces, as though I could write this:[code]SELECT 1 FROM[dbo].[table][/code]It's not as well written, from my opinion as - plus you're less likely to get into trouble...[code]SELECT 1 FROM [dbo].[table][/code]

RE: Transaction rowcount

EugeneMoulin — Thu, 05 Aug 2010 09:34:07 GMT

Ok, i didn't run the script of the question, but i thought the blank between end of line and "--" was mandatory.Thanks anyway for your answer and explanation.EuG

RE: Transaction rowcount

Oleg Netchaev — Thu, 05 Aug 2010 09:29:07 GMT

[quote][b]Fozzie (8/5/2010)[/b][hr]What I've found a bit weird is, if you run SELECT @@ROWCOUNT by itself in a completely fresh window then you get 1 returned... slightly odd behaviour?[/quote]I think that there was a QotD about this in the past and it generated a lot of posts due to this behaviour. It is actually [i][b]by design[/b][/i], and it all depends on what happens when you open first window. For example, if you open SSMS, open new window then [b]@@rowcount[/b] is [b]1[/b], but if you after opening new window go after database list dropdown and pick your database then [b]select @@rowcount[/b] returns [b]0[/b] simply because clicking in the dropdown silently issued[b] use [your_db_name];[/b] prior to you running [b]select @@rowcount[/b]. Whatever settings are silently ran by SSMS depends on things such as your user options, database options, SSMS version etc. The bottom line is that when you open SSMS and [b]do[/b] see [b]select @@rowcount[/b] returns [b]1[/b] out of the bat then it simply means that the latest silently executed for you by SSMS script affected one record.Oleg

RE: Transaction rowcount

Dennissinned — Thu, 05 Aug 2010 08:29:26 GMT

Nice question.

RE: Transaction rowcount

Fozzie — Thu, 05 Aug 2010 04:07:11 GMT

I may be getting confused with what you're saying..But all of this works fine[code]select @@rowcount-- check this one outselect @@rowcount -- check this one outselect @@rowcount -- check this one outselect @@rowcount -- check this one out[/code]These would not work[code]select @@rowcount- - check this one outselect rowcount -- check this one outselect @@rowcount -- check this one outselect @@rowcount -- checkthis one out[/code]We've a policy at work to use /* */ for comments where possible in case peeps run comments over a line... but it's also a pain in the backside if you want to comment out a block with these already in.What I've found a bit weird is, if you run SELECT @@ROWCOUNT by itself in a completely fresh window then you get 1 returned... slightly odd behaviour? For example run the below script, I've used different commenting in case of line wrapping.[code]SELECT @@ROWCOUNT /* Will return 1, even though we've not previousiy run a statement */SELECT 1 WHERE 2 = 1 /* Will an empty record set */SELECT @@ROWCOUNT /* Will return 0 as expected as the previous statement returned no rows */SELECT @@ROWCOUNT /* Will return 1 as the previous statement returned a row i.e of 0 */[/code]

RE: Transaction rowcount

EugeneMoulin — Thu, 05 Aug 2010 03:51:39 GMT

Ok, but i thought there was a missing blank between "rowcount" and "--"...EuG

RE: Transaction rowcount

Fozzie — Thu, 05 Aug 2010 03:09:31 GMT

The only errors encounted would be if you didn't have the database and table already created as the script relies on them existing.There's nothing wrong with SELECT @@ROWCOUNT -- a comment

RE: Transaction rowcount

sakthimadan — Thu, 05 Aug 2010 02:58:53 GMT

No.. i'm not getting any error..are you??

RE: Transaction rowcount

EugeneMoulin — Thu, 05 Aug 2010 02:19:22 GMT

Nice question, but, if we run the given script, don't we have an error on this line ?[code="sql"]select @@rowcount-- check this one out[/code]EuG

RE: Transaction rowcount

SQLRNNR — Tue, 03 Aug 2010 15:01:28 GMT

Nice question. Thanks.

RE: Transaction rowcount

Steve Cullen — Tue, 03 Aug 2010 10:30:44 GMT

Yep. @@rowcount is a cruel mistress. I avoid her like a rash. :-D

RE: Transaction rowcount

sakthimadan — Tue, 03 Aug 2010 02:20:07 GMT

Yeah thanks...Now i got it...Thanks for your reply..!!!

RE: Transaction rowcount

Fozzie — Tue, 03 Aug 2010 02:09:34 GMT

sakthi_madan,Run in query analyser:SELECT @@ROWCOUNTSELECT @@ROWCOUNTThe first statement returns a row with 0 The second statement is the count of rows returned from the first, i.e 1 row of 0.Hope that makes sense.I got the answer wrong as well... so close yet so far. Good question.Kev

RE: Transaction rowcount

sakthimadan — Mon, 02 Aug 2010 21:29:12 GMT

Can anyone explain me, how does it return '1' as the last rowcount???

RE: Transaction rowcount

Robert Frasca — Mon, 02 Aug 2010 13:58:17 GMT

Nice question. You got me with the last SELECT setting the value to 1. I thought it returned 0.

RE: Transaction rowcount

pankaj upadhyay — Mon, 02 Aug 2010 00:08:20 GMT

good question

RE: Transaction rowcount

udayroy15 — Sun, 01 Aug 2010 23:55:10 GMT

nice!! but tough to concentrate........

RE: Transaction rowcount

ziangij — Sun, 01 Aug 2010 23:13:32 GMT

a very good question... thanks :-)

RE: Transaction rowcount

UMG Developer — Sun, 01 Aug 2010 23:04:26 GMT

Nice question, thanks!

RE: Transaction rowcount

Oleg Netchaev — Sun, 01 Aug 2010 20:57:12 GMT

Nice question, I liked that it is clean and straightforward, no tricks. The column names in that table of yours are not very nice though. :-DOleg

RE: Transaction rowcount

bitbucket-25253 — Sat, 31 Jul 2010 15:15:53 GMT

Nice question

Transaction rowcount

Repriser-991084 — Sat, 31 Jul 2010 13:22:42 GMT

Comments posted to this topic are about the item [B]Transaction rowcount[/B]