SELECT

  • ...and if you check out the question statistics - that's what most people are doing - getting it, erm, 'right'.

  • Whoa there Master Hugo - where did that lecture come from? You're just doing this to get extra points really... 😀

  • Never mind the point, but the answer should be correct (2,0).

  • kyliedude (10/5/2012)


    where did that lecture come from?

    It wasn't intended as a lecture. It was just me letting off steam after seeing too many messages (not only in this topic, many topics have the same issue) of people "demanding" "their" points. I simply don't get why those points matter to people.

    It was not my intention to single you out, or to offend you. My apologies if you did feel offended.

    I do still hope that people will stop and consider exactly how important those points really are, and if that warrants the tone of some of the messages I am seeing.

    (Hmm, so maybe it was a lecture after all.. ;))


    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/

  • 🙁

    Question: How many rows are returned from the selects listed above?

    Explanation: You cannot insert null values into a not nullable column.

    Everyone knows that.

    So what is the gain from this question? Even trivial one, I still do not have answer how many rows should the two selects return.

    Igor Micev,My blog: www.igormicev.com

  • A deceptively simple question, +1 for me - thanks. Perhaps slightly better wording next time though please. 2,0 is also, technically, correct according to the terms of your question.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • Another confusing QotD! The answer is definitely 2,0. That is the results from the "selects". The "insert" errors out. It is very straight-forward.

  • The question should have been either how many rows are inserted which would match with the 'right' answer or 2,0 should be the correct answer.

  • Add one more to the list of people who answered 2, 0 for the reasons given.

    I was just tickled that I knew the really correct answer this time, knew that it would also throw an error and why, and that there would be more than a little discussion over in the forum about it. 🙂 All before I submitted my answer.

    I'd be doggone if dropping by every day isn't teaching me something. Thanks folks. 😉

  • Sorry, but there question is about the Select's. The error happened on Insert and not in SELECT, so the correct answer would be 2.0.

  • Hugo Kornelis (10/5/2012)


    I *knew* when I saw the answer options that there would be a possible source of confusion here.

    Yes, the insert into the second table will fail. But that insert is not batch-aborting. The second select will still run, and because the insert has failed, it will return an empty set.

    The answer is: "How many rows are returned from the selects listed above?" (emphasis mine). The only correct answer is 2,0, since the number of rows returned by the second row is not equal to "error" (which is not even a number), but "zero".

    I agree. Based on the wording of the question, the answer really should be "2,0". Both select statements run without error.

  • Hugo Kornelis (10/5/2012)


    I *knew* when I saw the answer options that there would be a possible source of confusion here.

    Yes, me too. So to make sure whether the second select would run, I actually ran the code.

    Yes, the insert into the second table will fail. But that insert is not batch-aborting. The second select will still run, and because the insert has failed, it will return an empty set.

    Which my test verified.

    The answer is: "How many rows are returned from the selects listed above?" (emphasis mine). The only correct answer is 2,0, since the number of rows returned by the second row is not equal to "error" (which is not even a number), but "zero".

    Agreed. Furthermore, to be pedantic, if the SELECT statement never ran, it would still have returned 0 rows, so the correct answer would still be 2,0.

  • demonfox (10/4/2012)


    Lokesh Vij (10/4/2012)


    sam.dahl (10/4/2012)


    I recognised the attempt to insert null and hence picked 2,0 because that's what the two select statements return: 2 and 0. It's the insert statement that fails.

    +1

    I also misunderstood the question.

    well , I got it right .. I guess ; I didn't consider the select statement returning nothing..

    But I would say , 2,0 should be the valid answer , since that is actually being asked in the question..

    +1

    +1

    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

  • Just restating what has already been discussed: the error was on the insert, not the select statement. If you run the same select statement ten more time, you will not get ten more errors.

    The question did make me think about not nulls, but the wording in the question did ask for the "SELECTS".

  • Good Question,

    I am thinking in different way in terms of table variable even I did not look at not null constraint

Viewing 15 posts - 16 through 30 (of 57 total)

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