PARSE 1

Question

PARSE 1

Ron McCullough

SSC Guru

Points: 63877
More actions
December 15, 2012 at 1:36 pm

#286220

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]

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

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

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

Thomas Abraham SSChampion Points: 10761 More actions · Answer 2

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

john.arnott SSChampion Points: 11882 More actions · Answer 3

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.

Lokesh Vij SSChampion Points: 10836 More actions · Answer 4

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

DugyC Hall of Fame Points: 3804 More actions · Answer 5

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]

JoseACJr SSC Eights! Points: 934 More actions · Answer 6

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 ''.

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 7

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

rhythm.varshney Ten Centuries Points: 1138 More actions · Answer 8

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.

DavidBridgeTechnology.com SSChasing Mays Points: 649 More actions · Answer 9

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

TomThomson SSC Guru Points: 104773 More actions · Answer 10

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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 11

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 12

Gazareth

One Orange Chip

Points: 27737

December 17, 2012 at 6:39 am

#1569123

Nice question, thanks.

Dana Medley SSCertifiable Points: 6764 More actions · Answer 13

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

Everything is awesome!

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 14

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/