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 Tuesday, December 18, 2012 4:09 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 12, 2014 4:19 AM
Points: 701, Visits: 1,145
Confused me. Do not have 2012 to play with. On 2008 this thing errors.
Post #1397677
Posted Wednesday, December 19, 2012 12:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:07 PM
Points: 11,194, Visits: 11,109
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1398204
Posted Wednesday, December 19, 2012 12:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:07 PM
Points: 11,194, Visits: 11,109
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1398205
Posted Wednesday, December 19, 2012 6:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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
Post #1398350
Posted Wednesday, December 19, 2012 6:20 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 12, 2014 4:19 AM
Points: 701, Visits: 1,145
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.
Post #1398352
Posted Wednesday, December 19, 2012 6:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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
Post #1398353
Posted Wednesday, December 19, 2012 6:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
(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
Post #1398356
Posted Wednesday, December 19, 2012 11:07 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 8,573, Visits: 9,081
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
Post #1398572
Posted Thursday, December 20, 2012 9:19 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 575, Visits: 3,362
i didn't know the answer, so i picked 5 [almost] at random and got it right

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
Post #1399030
Posted Tuesday, January 15, 2013 1:43 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 8:54 PM
Points: 606, Visits: 151
Nice Question But Miss it
Post #1407480
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse