Rowcount
-
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 -
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.
-
well i thought about it and then ran it to confirm my theory (my theory was 0,1) and the result said otherwise
-
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
-
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.
-
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:
-
i believe it has been corrected to include 1,1 as a correct answer
-
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 -
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.)
-
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.
-
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.
-
mukeshkane (1/29/2010)
I also checked it on 2005 and the result is 1,1how 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
-
This is a poorly worded question.
It does not state the conditions of the question.
Tried it on my 2000 server.
Opened a Query Analyzer window.
Entered both lines.
Pressed F5 to run.
I got 0,1
All subsequent runs give 1,1
If I save the two select statements into a sql script. Close Query Analyzer. Open Query Analyzer. Open and execute the script. I get 0,1.
This is a poorly worded question.
When I first looked at the question, my assumption was that we were in a fresh environment/session and that nothing else had been run.
If I had just run a query prior to executing these two lines, the first select would have returned the rowcount from that query. Thus I assumed that the question implied that nothing had been run prior to the first select.
-
Wow, so many replies - I'd chime in and say the answer is:
NULL
Reason:
The previous statement is unknown, therefore the outcome of the first SELECT @@ROWCOUNT is unknown, therefore the whole answer to this question is unknown.
Q.E.D.
Edit: For the posters STILL complaining that 1,1 should be the result or 0,1 should be the result:
Read the whole thread, especially the posts about the trace (profiler) you can use to identify the last statements that were executed by your tool of choice (SSMS, sqlcmd, osql, QA or else) upon connection. There will almost always be at least one statement affecting @@ROWCOUNT (see msdn link for statements that affect @@ROWCOUNT) and the last one will be determining the results you get in your specific case.
Best Regards,
Chris Büttner
-
bitbucket-25253 (1/29/2010)
malleswarareddy_m The question is improper
Rick-153145 Another rubbish QoTD.
bluesbiker I agree with user "malleswarareddy_m", this question is improper
To malleswarareddy_m, Rick-153145 and bluesbiker let me issue you a challenge.
Each of you submit a QOD and lets see how well you do
[/size]
I agree with:
kaspencer So, if you are considering writing a QotD, just remember: you are under a definite obligation to ensure that the answer is predictable and will be produced on the vast majority of instances and versions (unless conditions are specifically mentioned).
Having submitted 25 QODs, 22 of which have been published, 2 are scheduled for March and 1 pending let me state "It is not an easy task"
I would add to kaspencer's statement, the following addition: test, test and test again your proposed submission before submitting. Let a few days pass and retest, retest and retest to discover, if any, assumptions or unforseen events that might influence the correct answer(s)
With all that said and done, the QOD did fullfill one prime goal -- It taught a lot of people something they did not know before.
One step bitbucket forgot to mention - Have somebody review the question and offer suggestions too
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 91 through 105 (of 130 total)
You must be logged in to reply to this topic. Login to reply