Post reply

Rowcount

  • January 29, 2010 at 8:18 am

    #1110768

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

    select @@ROWCOUNT

    ...where this returns 0, 1.

    declare @ChangeRowCount bit

    set @ChangeRowCount = 1

    print @ChangeRowCount

    select @@ROWCOUNT

    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

  • January 29, 2010 at 8:19 am

    #1110769

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

  • January 29, 2010 at 8:24 am

    #1110772

    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.

  • January 29, 2010 at 8:28 am

    #1110775

    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

  • January 29, 2010 at 8:29 am

    #1110777

    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.

  • January 29, 2010 at 8:31 am

    #1110778

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

  • January 29, 2010 at 8:33 am

    #1110783

    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

  • January 29, 2010 at 8:44 am

    #1110788

    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

    1

    when run.

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

  • January 29, 2010 at 8:51 am

    #1110795

    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:

  • January 29, 2010 at 8:51 am

    #1110796

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

  • January 29, 2010 at 8:54 am

    #1110799

    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

  • January 29, 2010 at 8:57 am

    #1110802

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

  • January 29, 2010 at 9:00 am

    #1110804

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

  • January 29, 2010 at 9:01 am

    #1110805

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 29, 2010 at 9:09 am

    #1110811

    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

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

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