Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1718192021»»»

That case filter Expand / Collapse
Author
Message
Posted Monday, July 25, 2011 9:54 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, September 22, 2014 3:13 PM
Points: 774, Visits: 1,179
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.
Post #1147641
Posted Monday, July 25, 2011 10:25 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
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
Post #1147669
Posted Monday, July 25, 2011 11:12 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, September 22, 2014 3:13 PM
Points: 774, Visits: 1,179
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
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.
Post #1147730
Posted Monday, July 25, 2011 12:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Wednesday, October 22, 2014 12:34 PM
Points: 31,181, Visits: 15,626
I have awarded back all points to date for this.

I have also reworded the question to note that one batch is needed.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1147768
Posted Monday, July 25, 2011 12:45 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 3,466, Visits: 1,820
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 Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com
Post #1147809
Posted Tuesday, July 26, 2011 12:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 31, 2013 9:20 PM
Points: 18, Visits: 98
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
Post #1148014
Posted Tuesday, July 26, 2011 2:02 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 3:38 AM
Points: 379, Visits: 690
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
----------------------------------------
Post #1148059
Posted Tuesday, July 26, 2011 3:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 13,295, Visits: 11,086
Great question! Not easy to spot, but hey, we like a little challenge



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1148092
Posted Tuesday, July 26, 2011 5:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 31, 2013 9:20 PM
Points: 18, Visits: 98
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 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.


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
Post #1148758
Posted Wednesday, July 27, 2011 2:03 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 3:38 AM
Points: 379, Visits: 690
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
----------------------------------------
Post #1148889
« Prev Topic | Next Topic »

Add to briefcase «««1718192021»»»

Permissions Expand / Collapse