Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

PARSE 1 Expand / Collapse
Author
Message
Posted Monday, December 17, 2012 5:38 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 8,726, Visits: 9,277
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
Post #1397209
Posted Monday, December 17, 2012 5:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:47 AM
Points: 2,840, Visits: 3,872
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 I better get back to bed...
Now where is the internet delete button when you need it?


Best Regards,
Chris Büttner
Post #1397214
Posted Monday, December 17, 2012 6:39 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:20 AM
Points: 1,945, Visits: 3,204
Nice question, thanks.
Post #1397233
Posted Monday, December 17, 2012 7:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:56 AM
Points: 1,860, Visits: 1,398
Thanks for the great question Ron. I have definitely learned something today.



Everything is awesome!
Post #1397246
Posted Monday, December 17, 2012 7:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 5,977, Visits: 8,237
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1397254
Posted Monday, December 17, 2012 11:04 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:23 AM
Points: 3,299, Visits: 1,978
Thanks for the 2012 question.
Post #1397355
Posted Monday, December 17, 2012 2:03 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:50 AM
Points: 4,425, Visits: 3,417
Great questioon - thanks, Ron!
Post #1397417
Posted Monday, December 17, 2012 4:17 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
Thanks Ron - Good stuff



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1397466
Posted Monday, December 17, 2012 7:24 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 8,726, Visits: 9,277
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
Post #1397504
Posted Monday, December 17, 2012 11:04 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 4, 2014 5:38 AM
Points: 945, Visits: 558
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..
Post #1397559
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse