SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


PARSE 1


PARSE 1

Author
Message
Tom Thomson
Tom Thomson
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51090 Visits: 13160
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
Christian Buettner-167247
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8569 Visits: 3889
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
Gazareth
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15959 Visits: 6114
Nice question, thanks.
Dana Medley
Dana Medley
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4314 Visits: 1707
Thanks for the great question Ron. I have definitely learned something today.



Everything is awesome!
Hugo Kornelis
Hugo Kornelis
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34646 Visits: 13126
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: http://sqlblog.com/blogs/hugo_kornelis
Ken Wymore
Ken Wymore
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9662 Visits: 2470
Thanks for the 2012 question.
Revenant
Revenant
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22625 Visits: 5137
Great questioon - thanks, Ron!
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: General Forum Members
Points: 145749 Visits: 18652
Thanks Ron - Good stuff

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Learn Extended Events

Tom Thomson
Tom Thomson
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51090 Visits: 13160
Hugo Kornelis (12/17/2012)
(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).

Good point. I was wrong to suggest that 1 row might be returned. Returning 1 row is not a possibility, only returning 0 rows or returning 2 rows, since the order by clause means that there is a sort either before the projection (so there will be two rows) or after the projection has been done for all rows (in which case there will be 0 rows).

Tom

Dineshbabu
Dineshbabu
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1918 Visits: 569
Hi,

Before answering the question i reffered the below mentioned blog and got it correct.
http://blog.sqlauthority.com/2011/09/07/sql-server-denali-conversion-function-try_parse-a-quick-introduction/

Eventhough u get error message for Parse(), first two rows will be converted to datetime and u can see that in results.
This is the basic functionality of CAST and CONVERT too..

--
Dineshbabu
Desire to learn new things..
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search