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


PARSE 1


PARSE 1

Author
Message
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25889 Visits: 12494
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
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5265 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
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7686 Visits: 6045
Nice question, thanks.
Dana Medley
Dana Medley
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

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



Everything is awesome!
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18745 Visits: 12426
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
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6577 Visits: 2396
Thanks for the 2012 question.
Revenant
Revenant
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12467 Visits: 5010
Great questioon - thanks, Ron!
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66261 Visits: 18570
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

Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25889 Visits: 12494
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
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1434 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