July 25, 2011 at 9:54 am
john.arnott (7/22/2011)
jlennartz (7/22/2011)
Yep, I got it wrong, also. But to me I got it right because I figured out it would have inserted 5 rows, as started.You beat me on that point. I stopped trying to decide between 5 and 8 when I realized it would return zero rows. Did you you see that and decide it was a slip on Ninja's part? Or did you concentrate on the case statements and let the fact that no rows are returned slip by? Either way, this QOD teaches various lessons, don't you think?
I totally agree. I had missed that it would not return any rows because I was concentrating on what was being inserted. I am still to much of a novice to readily pickup on that kind of error. Maybe someday.
July 25, 2011 at 10:25 am
jlennartz (7/25/2011)
john.arnott (7/22/2011)
jlennartz (7/22/2011)
Yep, I got it wrong, also. But to me I got it right because I figured out it would have inserted 5 rows, as started.You beat me on that point. I stopped trying to decide between 5 and 8 when I realized it would return zero rows. Did you you see that and decide it was a slip on Ninja's part? Or did you concentrate on the case statements and let the fact that no rows are returned slip by? Either way, this QOD teaches various lessons, don't you think?
I totally agree. I had missed that it would not return any rows because I was concentrating on what was being inserted. I am still to much of a novice to readily pickup on that kind of error. Maybe someday.
We all started there... just keep going at it and someday you'll see those from miles away.
P.S. Over 85% of people got this question wrong (see my conclusion)so maybe you can get some confort in that figure :w00t:
July 25, 2011 at 11:12 am
Ninja's_RGR'us (7/25/2011)
jlennartz (7/25/2011)
john.arnott (7/22/2011)
jlennartz (7/22/2011)
Yep, I got it wrong, also. But to me I got it right because I figured out it would have inserted 5 rows, as started.You beat me on that point. I stopped trying to decide between 5 and 8 when I realized it would return zero rows. Did you you see that and decide it was a slip on Ninja's part? Or did you concentrate on the case statements and let the fact that no rows are returned slip by? Either way, this QOD teaches various lessons, don't you think?
I totally agree. I had missed that it would not return any rows because I was concentrating on what was being inserted. I am still to much of a novice to readily pickup on that kind of error. Maybe someday.
We all started there... just keep going at it and someday you'll see those from miles away.
P.S. Over 85% of people got this question wrong (see my conclusion)so maybe you can get some confort in that figure :w00t:
Thanks but no comfort needed as I approach every QoTD as a learning experience. If I get it right I will have had to research it a bit to get the answer and if I get it wrong I research, usually by reading the comments to understand why. Thanks for the question.
Points are just a way of keeping track, the prize is the knowledge gained.
July 25, 2011 at 12:02 pm
I have awarded back all points to date for this.
I have also reworded the question to note that one batch is needed.
July 25, 2011 at 12:45 pm
I have to say I got the question wrong, and I'm one of those people who thought the question had more to do with a unioned query pulling against the table you are inserting into. Unfortunately I don't see any way the question could have been made more clear, its (in my humble opinion) just a matter of interpretation.
That being said, On a note similar to what Ninja's_RGR'us was looking for (not code), I just finished helping someone set up alerts for some SQL jobs. We spent about 10 minutes setting up the operator, and the alerts etc, then setting up the DBMail itself when it turned out it wasn't set up yet.
We then spent a couple of hours trying to debug why it wasn't working. We had finally broken for lunch when I realized we hadn't gone into the Agent properties and turned Alerts on. Talk about a waste of time.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
July 26, 2011 at 12:03 am
I said returned, not selected those are NOT synonyms). There's no dataset being returned so 0 is the correct answer.
However, returning no dataset is not strictly the same as returning zero rows. I would say the correct answer is "undefined", which was not an option 🙁
Probably the most correct option in my opinion is "it depends", because it does - on whether you consider no dataset to be the same as zero rows!
ROSCO
July 26, 2011 at 2:02 am
Ross Crawford (7/26/2011)
I said returned, not selected those are NOT synonyms). There's no dataset being returned so 0 is the correct answer.
However, returning no dataset is not strictly the same as returning zero rows.
not sure about that one.
run this:
set nocount on
declare @Foo int
select @Foo = 1 where 1 = 2
--select @@rowcount
then run this:
set nocount on
declare @Foo int
select @Foo = 1 where 1 = 2
select @@rowcount
This demonstrates that @@rowcount considers a lack of output as 0 rows, not NULL rows as some have been suggesting.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
July 26, 2011 at 3:09 am
Great question! Not easy to spot, but hey, we like a little challenge 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 26, 2011 at 5:29 pm
BenWard (7/26/2011)
Ross Crawford (7/26/2011)
I said returned, not selected those are NOT synonyms). There's no dataset being returned so 0 is the correct answer.
However, returning no dataset is not strictly the same as returning zero rows.
not sure about that one.
Me either, that's why I think "it depends" is the most accurate answer.
run this:
set nocount ondeclare @Foo int
select @Foo = 1 where 1 = 2
--select @@rowcount
then run this:
set nocount ondeclare @Foo int
select @Foo = 1 where 1 = 2
select @@rowcount
This demonstrates that @@rowcount considers a lack of output as 0 rows, not NULL rows as some have been suggesting.
Now run this:
set nocount on
--declare @Foo int
--select @Foo = 1 where 1 = 2
select @@rowcount
I'm guessing because @@rowcount is an INT, the developers had to decide what to return when there was no logical way to return a number of rows, and they chose to return zero. Doesn't necessarily mean they made the right choice.
This is irrelevant anyway, as the question was "How many rows are returned by executing all of the following in one batch?", not "What is the value of @@rowcount after executing all of the following in one batch?". Note that @@rowcount returns the number of rows affected, not necessarily the number of rows returned, so adding SELECT @@rowcount to the end of the original SQL returns 5, not 0.
ROSCO
July 27, 2011 at 2:03 am
I know rowcount would have returned 5, my point was that Microsoft's developers themselves consider no results grid to equal 0 rows not null rows.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
July 27, 2011 at 4:46 am
This is just a fowl joke :doze:
How many rows are returned
None as there is no Select as the code is given. There is no correct answer (0 is not an option) :exclamationmark:
by executing all of the following in one batch
Table is in variable. How can be insert executed without declare :ermm:
case filter
Case is not considered because the table is still empty at the time of execution.
(copy / paste error)
How the hell could anyone see this coming :sick:
It seems the answer depends on what was someone thinking on how to play a prank on QOTD community :w00t:
July 27, 2011 at 4:51 am
dawryn (7/27/2011)
by executing all of the following in one batch
Table is in variable. How can be insert executed without declare :ermm:
There was a declare. It was at the top of the query:
DECLARE @openorder INT
SET @openorder = 2
DECLARE @tbl TABLE (openorder INT)
July 27, 2011 at 5:16 am
dawryn (7/27/2011)
This is just a fowl joke :doze:How many rows are returned
None as there is no Select as the code is given. There is no correct answer (0 is not an option) :exclamationmark:
by executing all of the following in one batch
Table is in variable. How can be insert executed without declare :ermm:
case filter
Case is not considered because the table is still empty at the time of execution.
(copy / paste error)
How the hell could anyone see this coming :sick:
It seems the answer depends on what was someone thinking on how to play a prank on QOTD community :w00t:
Thank you for reading my full explaination (my first post) and then the subsequent 150+ answers. All your questions have been adressed already.
July 27, 2011 at 8:19 am
BenWard (7/27/2011)
I know rowcount would have returned 5, my point was that Microsoft's developers themselves consider no results grid to equal 0 rows not null rows.
I'm glad the MS developers got it right. 😛 Of course I agree with them and with you. And of course with Ninja's_RGR'us.
In the UK it's taught to 5 and 6 year old children in infant school (I guess that's what USA calls grade 1 and grade 2 ?). They learn that an empty bag of beans contains 0 beans.
So when they grow up and become DBAs, the really should realise that if a statement doesn't return any rows, it returns 0 rows.
It's a pity some of the commenters on this topic apparently missed those early lessons on numbers and arithmetic. :w00t:
Tom
July 27, 2011 at 8:43 am
Tom.Thomson (7/27/2011)
BenWard (7/27/2011)
I know rowcount would have returned 5, my point was that Microsoft's developers themselves consider no results grid to equal 0 rows not null rows.I'm glad the MS developers got it right. 😛 Of course I agree with them and with you. And of course with Ninja's_RGR'us.
In the UK it's taught to 5 and 6 year old children in infant school (I guess that's what USA calls grade 1 and grade 2 ?). They learn that an empty bag of beans contains 0 beans.
So when they grow up and become DBAs, the really should realise that if a statement doesn't return any rows, it returns 0 rows.
It's a pity some of the commenters on this topic apparently missed those early lessons on numbers and arithmetic. :w00t:
I can't believe this is actually still being debated by the same people as last week (new people I'd understand... but still).
Did I inadvertently start a new "are the questions getting worse" thread?!?!?! 😀
Viewing 15 posts - 181 through 195 (of 200 total)
You must be logged in to reply to this topic. Login to reply