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


PARSE 1


PARSE 1

Author
Message
(Bob Brown)
(Bob Brown)
SSC Eights!
SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)

Group: General Forum Members
Points: 885 Visits: 1145
Confused me. Do not have 2012 to play with. On 2008 this thing errors.
Paul White
Paul White
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: 15814 Visits: 11355
L' Eomot Inversé (12/17/2012)
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).

No, you were right first time :-)

There are no guarantees. The execution engine might suppress errors until the row is assembled for despatch to the client, for example. It doesn't do this today, but it could.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
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: 15814 Visits: 11355
L' Eomot Inversé (12/17/2012)
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).

No, you were right first time :-)

There are no guarantees. The execution engine might suppress errors until the row is assembled for despatch to the client, for example. It doesn't do this today, but it could.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
StarNamer
StarNamer
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1650 Visits: 1992
I can't see anywhere in the question where it says this is supposed to be for 2012. Presumbly, it was thought that it's enough that TRY_PARSE and PARSE don't exist in 2008R2, however a hint would have been nice.

Derek
(Bob Brown)
(Bob Brown)
SSC Eights!
SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)

Group: General Forum Members
Points: 885 Visits: 1145
Derek Dongray (12/19/2012)
I can't see anywhere in the question where it says this is supposed to be for 2012. Presumbly, it was thought that it's enough that TRY_PARSE and PARSE don't exist in 2008R2, however a hint would have been nice.


I believe it should always be stated what version of SS the question addresses. Assumptions are always bad and there is no place for it in these questions.
StarNamer
StarNamer
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1650 Visits: 1992
I just tried it with 2008R2 and, as expected, got 2 error messages.

Msg 195, Level 15, State 10, Line 12
'TRY_PARSE' is not a recognized built-in function name.
Msg 195, Level 15, State 10, Line 15
'PARSE' is not a recognized built-in function name.



Unfortunately, I can't remember what my 5th answer was, as only 4 apply! I think I misread an answer about NULL values.

1. Select #1 does NOT Return a row with a NULL value for Xday
2. Select # 1 Returns an error message
3. Select # 2 Returns an error message
4. Select #2 does NOT Return a row with a NULL value for Xday

Derek
StarNamer
StarNamer
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1650 Visits: 1992
(Bob Brown) (12/19/2012)
I believe it should always be stated what version of SS the question addresses. Assumptions are always bad and there is no place for it in these questions.


I haven't been around for a while, but thought this was something that was recommended long ago when there were discussions about QOTDs which gave different answers depending on whether it was 2005 or 2008. It seems the same is happening with 2012.

Derek
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14350 Visits: 12199
Derek Dongray (12/19/2012)
(Bob Brown) (12/19/2012)
I believe it should always be stated what version of SS the question addresses. Assumptions are always bad and there is no place for it in these questions.


I haven't been around for a while, but thought this was something that was recommended long ago when there were discussions about QOTDs which gave different answers depending on whether it was 2005 or 2008. It seems the same is happening with 2012.

I thought the preferred idea was that questions which work for all versions which currently in standard support don't need to specify a version, while anything else does; so a question like this should specify version 2012, since versions 2008 and 2008R2 are still in standard support but the question doesn't work for these versions. We had the discussion when 2008 was released, because some questions didn't work for 2000, and again when 2000 dropped out of standard support. But people who weren't following QotD back then missed those discussions so don't know about that preference. (The discussion probably also happened when 2005 wqas released - I don't know, I wasn't aware of SQLServerCentral.com back then). So it doesn't seem possible to have a hard and fast rule without an editor putting a lot of effort into vetting questions, and since the SQLServerCentral editor has more important things to do for the site there can be no hard and fast rule.

Tom

davidandrews13
davidandrews13
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1068 Visits: 4542
i didn't know the answer, so i picked 5 [almost] at random and got it right w00t

i chose that #1 returned a row with NULL so thought that, by default, #2 must not return a NULL value
that instantly discounts #1 not returning NULL and #2 returning null

i guessed that #1 would return 3 rows, so #2 couldn't possibly by 3 rows also, so it must be 2

i then guessed that #2 returns an error message
asifkareem
asifkareem
SSChasing Mays
SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)

Group: General Forum Members
Points: 655 Visits: 151
Nice Question But Miss it
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