CHOOSE - 1

  • Christian Buettner-167247 (1/28/2013)


    Don't have AdventureWorks either... 🙁

    You know, google and 5 minutes of download time and you can get it... MS puts it up on Codeplex for you



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • This question is alittle difficult to answer when you don't have SQL Server 2012 and the AdventureWorks database installed. So there is no logic in determining the appropriate answer, and I had to guess and take a hit to my percentage just to post a reply.

    My Co-worker and I use these questions to test our knowledge - and do so without executing the commands. This question kind of required you to execute the command, and if you were able to execute then you shouldn't get the wrong answer.

    So, to me this was not a logic question and can hardly add to the bragging rights of your co-workers. My 2 cents.

  • mtassin (1/28/2013)


    Christian Buettner-167247 (1/28/2013)


    Don't have AdventureWorks either... 🙁

    You know, google and 5 minutes of download time and you can get it... MS puts it up on Codeplex for you

    Some of us work in environments where downloading and installing stuff from the internet is frowned upon, and such a frivolous exercise could incur the wrath of the Internet gate-keepers ... and keeping unnecessary databases (such as AdventureWorks) on the servers is seen as a waste of valuable resources.

    In these circumstances, similar to others, I have asumed a more or less even distribution of hire dates, and concluded the most hires would occur where more months were covered in the 'Group By' clause (Winter, in this case correct) and the least where least months were in the aggregate (Spring, and apparently incorrect)

    Without access to the underlying data, the question is more instructive as a lesson about the assumptions we make about our clients access to resources.

  • Interestingly, if you change the seasons to be three each on the correct months it gives the same result as the correct answer. (Correct months as per USA seasons, inferred from the use of 'Fall' instead of 'Autumn' and given SSC is a US based site).

    I suspect if the question had 'Fall' as 2 months and Winter as 4 months then most people would have chosen the correct answer, and that's probably what the author intended.

    A lot of people are complaining about not having SQL Server 2012 or Adventureworks on production machines. I have a test/dev VM with SQL Server 2012 SP1 and both the Adventureworks2012 and AdventureworksDW2012 databases. I would have thought this would be something SQL Server professionals should all do so that you can start to become familiar with the new functionality prior to any potential upgrade of your systems, or even just to keep your skills up to date. If not then this question might be useful in that it serves as a prompt to do that. If you can't install stuff at work and you don't want to do it on your off time, then why not get a free cloud trial with Azure or something else so you can try it out. I'm pretty sure I've read Steve Jones suggesting exactly that a few times.

  • Got one of the choices wrong but good to learn a new function..Thx..

  • mtassin (1/28/2013)


    Christian Buettner-167247 (1/28/2013)


    Don't have AdventureWorks either... 🙁

    You know, google and 5 minutes of download time and you can get it... MS puts it up on Codeplex for you

    Thank you for the solution to a problem that I don't have.

    :ermm:

    Best Regards,

    Chris Büttner

  • zerko (1/28/2013)


    mtassin (1/28/2013)


    Christian Buettner-167247 (1/28/2013)


    Don't have AdventureWorks either... 🙁

    You know, google and 5 minutes of download time and you can get it... MS puts it up on Codeplex for you

    Some of us work in environments where downloading and installing stuff from the internet is frowned upon, and such a frivolous exercise could incur the wrath of the Internet gate-keepers ... and keeping unnecessary databases (such as AdventureWorks) on the servers is seen as a waste of valuable resources.

    In these circumstances, similar to others, I have asumed a more or less even distribution of hire dates, and concluded the most hires would occur where more months were covered in the 'Group By' clause (Winter, in this case correct) and the least where least months were in the aggregate (Spring, and apparently incorrect)

    Without access to the underlying data, the question is more instructive as a lesson about the assumptions we make about our clients access to resources.

    +1 !

  • Like several others, I don't like this question. Not one bit. There's no sane way to answer it without runing code, and for those of us without sql 2012 there's no guarantee that any code we write and run will deliver the right answ3er, as we can't install AdventureWorks2012. I just can't see the point of a question like this.

    I got it right by guessing that AdventureWorks2008R2 had data sufficiently similar data in that table. I already knew that my AdventureWorks is significantly different from Ron's (to start with, it has a different collation) because one of the differences caused me to get one of Ron's previous questions wrong (I answered based on what I knew was the collation of my version, because Ron had specified neither the version of AdventureWorks nor the collation - so that too was a bad question), so that was a dangerous assumption, but it turned out to be correct this time. I couldn't of course run the code, since I don't have SQL2012, and I wasn't going to count about 300 rows by hand, so I spent about 15 seconds typing a query that would tell me how many hire dates were in each month in 2008R2 version, and did the addition of 4,3,3,and 2 numbers by hand. Waste of my time, I reckon - but only a tiny bit of time wasted, so it could have been worse.

    Tom

  • davoscollective

    A lot of people are complaining about not having SQL Server 2012 or Adventureworks on production machines. I have a test/dev VM with SQL Server 2012 SP1 and both the Adventureworks2012 and AdventureworksDW2012 databases. I would have thought this would be something SQL Server professionals should all do so that you can start to become familiar with the new functionality prior to any potential upgrade of your systems, or even just to keep your skills up to date. If not then this question might be useful in that it serves as a prompt to do that. If you can't install stuff at work and you don't want to do it on your off time, then why not get a free cloud trial with Azure or something else so you can try it out. I'm pretty sure I've read Steve Jones suggesting exactly that a few times.

    Bolding added by this poster.

    +1

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Christian Buettner-167247 (1/29/2013)


    mtassin (1/28/2013)


    Christian Buettner-167247 (1/28/2013)


    Don't have AdventureWorks either... 🙁

    You know, google and 5 minutes of download time and you can get it... MS puts it up on Codeplex for you

    Thank you for the solution to a problem that I don't have.

    :ermm:

    Nice +1

  • bitbucket-25253 (1/29/2013)


    davoscollective

    A lot of people are complaining about not having SQL Server 2012 or Adventureworks on production machines. I have a test/dev VM with SQL Server 2012 SP1 and both the Adventureworks2012 and AdventureworksDW2012 databases. I would have thought this would be something SQL Server professionals should all do so that you can start to become familiar with the new functionality prior to any potential upgrade of your systems, or even just to keep your skills up to date. If not then this question might be useful in that it serves as a prompt to do that. If you can't install stuff at work and you don't want to do it on your off time, then why not get a free cloud trial with Azure or something else so you can try it out. I'm pretty sure I've read Steve Jones suggesting exactly that a few times.

    Bolding added by this poster.

    +1

    Ron, you won't like this but your l;ast post has provoked me into being absolutely blunt. Have you any reasonable justification for a question that requires people either to run the code or to manually calculate a complicated aggregate over 290 rows? If you haven't, and I can't imagine any weay in which youi could have, it seems to me that agreeing with the davoscollective comment is just ducking the issue, abdicating responsability for a pretty awful question with an explanation which doesn't address in the slightest way the only difficult part of the question (the actual calculation).

    Tom

  • bitbucket-25253 (1/29/2013)


    davoscollective

    A lot of people are complaining about not having SQL Server 2012 or Adventureworks on production machines. I have a test/dev VM with SQL Server 2012 SP1 and both the Adventureworks2012 and AdventureworksDW2012 databases. I would have thought this would be something SQL Server professionals should all do so that you can start to become familiar with the new functionality prior to any potential upgrade of your systems, or even just to keep your skills up to date. If not then this question might be useful in that it serves as a prompt to do that. If you can't install stuff at work and you don't want to do it on your off time, then why not get a free cloud trial with Azure or something else so you can try it out. I'm pretty sure I've read Steve Jones suggesting exactly that a few times.

    Bolding added by this poster.

    +1

    1. Deny the problem

    2. Blame it on the customer

    What does that remind me of? Oh yes, customer service in general...

    Don't get me wrong, I honor the fact that you submitted this QOTD (and the many great questions from you before this one, and the ones that are about to come). But I think this one went in the wrong direction.

    But maybe I have misinterpreted it. Because I was under the impression that it tries to teach you how to use Choose, not how to become a "SQL Server professional" by reviewing AdventureWorks during QOTDs.

    Best Regards,

    Chris Büttner

  • bitbucket-25253 (1/29/2013)


    davoscollective

    A lot of people are complaining about not having SQL Server 2012 or Adventureworks on production machines. I have a test/dev VM with SQL Server 2012 SP1 and both the Adventureworks2012 and AdventureworksDW2012 databases. I would have thought this would be something SQL Server professionals should all do so that you can start to become familiar with the new functionality prior to any potential upgrade of your systems, or even just to keep your skills up to date. If not then this question might be useful in that it serves as a prompt to do that. If you can't install stuff at work and you don't want to do it on your off time, then why not get a free cloud trial with Azure or something else so you can try it out. I'm pretty sure I've read Steve Jones suggesting exactly that a few times.

    Bolding added by this poster.

    +1

    I too think that every serious SQL Server professional (hmmm, that should a tautology) should have access to a sandbox SQL Server instance in an isolated environment, where (s)he can try out stuff without having to fear breaking any important things. And while it's not required to have AdventureWorks there, it definitely can be convenient.

    For me, that sandbox instance lives on my own laptop. Developer edition - costs only 50 bucks; I luckily don't have to pay, but otherwise I definitely would pay for it out of my own pocket without giving it as much as a second thought. Or, if I really had a hard time coughing up that amount, I would switch to Express Edition.

    But I still don't like the question at all. You could just as well have written: "download and install an evaluation edition of SQL Server 2012 if you don't have one yet, then download and install AdventureWorks 2012, then run the code below - what is the result". That would have been the exact same learning experience.

    As I (and others) have said before: if you want people to learn about the CHOOSE operator, give them a question that they can try to work out in their head. That will test them much more, and that will teach them much more.

    I am surprised to see that you responded to one post, but didn't respond in any way to the criticism from other posts. With that, you create the appearance of not caring about the criticism. You don't have to agree with me (a lot of people don't agree with me - ask my wife and children if you don't believe me, they disagree with me all the time! ;)), but I would hope that you at least acknowledge that you heard us, and tell us how you think about it.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Yes, it would be very interesting to learn more about CHOOSE. While I don't currently run 2012, I think a question that applied more to the functioning of the index argument in CHOOSE would help us to learn more from it.

    While an operating lab-type example is fine, running the query and getting the correct answer doesn't always necessarily solidify an understanding of the principles of the function's operation.

    As has been suggested before, having the data present in the question would have been a big asset, as I did attempt to download the 2008 version of AdventureWorks, but was met with incompatibility issues.

    Way too much trouble to answer a question.

    Thanks for the question and for bringing CHOOSE to the forefront.

  • Now seriously...

    what's the point of this QotD? It's not testing any knowledge of how CHOOSE works.

    If the aim of this is to push you to install SQL 2012 and AW database, then ok, but having (mandatory) to run the query to know the answer, the question is just pointless.

    I guess all members here can copy/paste, press F5 and choose the right two answers.

Viewing 15 posts - 31 through 45 (of 55 total)

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