December 17, 2012 at 11:04 am
Thanks for the 2012 question.
December 17, 2012 at 2:03 pm
Great questioon - thanks, Ron!
December 17, 2012 at 4:17 pm
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[/url]
Learn Extended Events
December 17, 2012 at 7:24 pm
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
December 17, 2012 at 11:04 pm
Hi,
Before answering the question i reffered the below mentioned blog and got it correct.
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..
December 18, 2012 at 4:09 am
Confused me. Do not have 2012 to play with. On 2008 this thing errors.
December 19, 2012 at 12:02 am
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
SQLkiwi blog
@SQL_Kiwi
December 19, 2012 at 12:02 am
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
SQLkiwi blog
@SQL_Kiwi
December 19, 2012 at 6:14 am
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
December 19, 2012 at 6:20 am
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.
December 19, 2012 at 6:23 am
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
December 19, 2012 at 6:27 am
(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
December 19, 2012 at 11:07 am
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
December 20, 2012 at 9:19 am
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
January 15, 2013 at 1:43 pm
Nice Question But Miss it
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply