PARSE 1

  • Comments posted to this topic are about the item PARSE 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]

  • Very very intesting question. Thanks Ron.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Been a long time since I was this happy to get a QOTD right. It wasn't that hard, but I wasn't sure of the deductive reasoning I was using. Thanks for the challenge Ron.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Another good QOD on a new feature of SQL 2012. Thanks, Ron.

    BTW, I'm sure some of those who chose wrong answers will want to blame Microsoft for screwing up the BOL examples for TryParse(). Very strange to see that on what turns out to be an easy to understand function once you read the description.

  • Very Good Question Ron!

    Keep pouring more of those SS2012 stuff..

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Thomas Abraham (12/16/2012)


    Been a long time since I was this happy to get a QOTD right. It wasn't that hard, but I wasn't sure of the deductive reasoning I was using. Thanks for the challenge Ron.

    +1 ... punching the air, almost like when I'd seen this weekends NFC East scores, I was. 😀

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • Hi,

    Select #2 Returns 2 rows ???

    I don´t think so..

    Error converting string value '2013DEC32 00:00:00' into data type datetime using culture ''.

  • JoseACJr (12/17/2012)


    Hi,

    Select #2 Returns 2 rows ???

    I don´t think so..

    Error converting string value '2013DEC32 00:00:00' into data type datetime using culture ''.

    I was also quite surprised to see this answer and would have expected the SELECT to be atomic (either all or nothing). I dont have a 2012 handy so if someone could test this please?

    Best Regards,

    Chris BĂźttner

  • Christian Buettner-167247 (12/17/2012)


    JoseACJr (12/17/2012)


    Hi,

    Select #2 Returns 2 rows ???

    I don´t think so..

    Error converting string value '2013DEC32 00:00:00' into data type datetime using culture ''.

    I was also quite surprised to see this answer and would have expected the SELECT to be atomic (either all or nothing). I dont have a 2012 handy so if someone could test this please?

    +2

    Ron, can you please remove the confusion.

  • Select #2 Returns 2 rows ???

    I don´t think so..

    Error converting string value '2013DEC32 00:00:00' into data type datetime using culture ''.

    Yep - I thought that too but also do not have 2012 handy to test.

    I am also looking for confirmation.

    I hate getting things wrong but if I do then I want to learn something!

    David Bridge
    David Bridge Technology Limited
    www.davidbridgetechnology.com

  • Mosty a good question, apart from the awful misprint - the table would not be created unless that was corrected!

    To all those talking about select being atomic: that's nonsense. Select returns rows until it hits an error serious enough to make it stop. This error is serious enough, but it hits the error on the third row, when it has already returned 2 rows.

    Of course this does point up another flaw in the question: the answer assumes that the select statement traverses the rows in a particular order; that is not an assumption that should be made, the data engine is free to process the rows in any order it likes, so the select could actually return 2 rows (which, with just these 3 rows in the table, it almost certainly will), or 1 row, or even no rows at all. People moan strongly about things that explicitly depend on order even when there are all sorts of checks built into the code to detect deviation from the expected order, so relying on an expected order without any checks, as does this answer, is crazy.

    Tom

  • L' Eomot InversĂŠ (12/17/2012)


    Mosty a good question, apart from the awful misprint - the table would not be created unless that was corrected!

    To all those talking about select being atomic: that's nonsense. Select returns rows until it hits an error serious enough to make it stop. This error is serious enough, but it hits the error on the third row, when it has already returned 2 rows.

    Of course this does point up another flaw in the question: the answer assumes that the select statement traverses the rows in a particular order; that is not an assumption that should be made, the data engine is free to process the rows in any order it likes, so the select could actually return 2 rows (which, with just these 3 rows in the table, it almost certainly will), or 1 row, or even no rows at all. People moan strongly about things that explicitly depend on order even when there are all sorts of checks built into the code to detect deviation from the expected order, so relying on an expected order without any checks, as does this answer, is crazy.

    Hm :Whistling: I better get back to bed...

    Now where is the internet delete button when you need it?

    Best Regards,

    Chris BĂźttner

  • Nice question, thanks.

  • Thanks for the great question Ron. I have definitely learned something today.



    Everything is awesome!

  • L' Eomot InversĂŠ (12/17/2012)


    Of course this does point up another flaw in the question: the answer assumes that the select statement traverses the rows in a particular order; that is not an assumption that should be made, the data engine is free to process the rows in any order it likes, so the select could actually return 2 rows (which, with just these 3 rows in the table, it almost certainly will), or 1 row, or even no rows at all.

    I considered putting in a similar content when I answered the question, but decided to let it slide.

    The ORDER BY in the queries forces the rows to be returned in the order in which they were inserted, with the third row being the error row. Without removing the ORDER BY, I have not been able to get SQL Server to return a different result set, as all things I tried ended up with a query plan where the rows were ordered first, before projecting the SELECT list.

    (That being said - there is indeed no guarantee. SQL Server could come up with an execution plan that projects the SELECT list first, then reorders the rows. In that case, the second query would return no rows - sorting in an execution plan is a blocking operation, so the sort operator will not start returning rows until it has consumed the entire input, and in this hypothetic case the query would error out before the entire input has been consumed).


    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/

Viewing 15 posts - 1 through 15 (of 31 total)

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