November 28, 2012 at 8:36 am
I believe I maybe have an answer as to why the behaviour of SET QUOTED_IDENTIFIER ON is different to the other set options, namely that the statement is issued at parse time whereas the others are issued at execute or run time. If you think about it, this needs to happen for Intellisense to work as it does. The implication is that if the SET statement is present, the setting takes effect, regardless of whether code execution actually reaches that point; and the SET statement takes effect before any statements are executed. For example, even if the set statement is in an IF...ELSE statement block that is never reached during execution, the SET statement still takes effect because the IF...ELSE statement block is parsed. The following illustrates the point:
SET QUOTED_IDENTIFIER OFF;
IF 1 = 2
BEGIN
SET QUOTED_IDENTIFIER ON
END;
select "'This will raise an error if OUOTED_IDENTIFIER is ON'" + "";
Edit: To get the code working as it should...
November 28, 2012 at 8:43 am
+ 1 on being confused.
November 28, 2012 at 9:03 am
I let this one through since it was interesting and the execution plan shows "Query 1", "Query 2", etc.
I suspect there is something happening here in the query processor that executes each of these items, albeit at a very low cost.
I've awarded back points to date and changed the question to ask for "queries in the execution plan" so that it is clear.
November 28, 2012 at 9:24 am
Steve Jones - SSC Editor (11/28/2012)
I let this one through since it was interesting and the execution plan shows "Query 1", "Query 2", etc.I suspect there is something happening here in the query processor that executes each of these items, albeit at a very low cost.
I've awarded back points to date and changed the question to ask for "queries in the execution plan" so that it is clear.
You might want to change that to "queries in the estimated execution plan". According to several reports in this topic, the actual execution plan does not show any queries for this batch.
November 28, 2012 at 9:32 am
Confused question, confused scores: the question expects a single answer, the current scores claim that 98 percent of respondents got it right. Where does that number come from?
November 28, 2012 at 9:41 am
Revenant (11/28/2012)
Confused question, confused scores: the question expects a single answer, the current scores claim that 98 percent of respondents got it right. Where does that number come from?
It is a little confusing to me too to see 98% correct answers but the selection breakout does not reflect that. I thought that the answer selection breakout would update along with the score? Shouldn't all the incorrect answer selections from earlier today have moved to choice 8? That way the %correct is reflected in the choice breakout as well?
November 28, 2012 at 9:44 am
KWymore (11/28/2012)
Revenant (11/28/2012)
Confused question, confused scores: the question expects a single answer, the current scores claim that 98 percent of respondents got it right. Where does that number come from?It is a little confusing to me too to see 98% correct answers but the selection breakout does not reflect that. I thought that the answer selection breakout would update along with the score? Shouldn't all the incorrect answer selections from earlier today have moved to choice 8? That way the %correct is reflected in the choice breakout as well?
As far as I know, the possibilities Steve has for changing thins after the fact are limited. He can only choose to give points to all who submitted a reply so far, without changing the answers. So at the point when Steve made the correction, correct answers become 100%; after that, people picking the wrong answer again cause the ratio to go down.
November 28, 2012 at 9:56 am
Confusing question. I got it right because I decided to go with the results of the estimated execution plan instead of the actual, but I agree the wording and explanation needed to be more clear.
Saludos,
November 28, 2012 at 9:58 am
good catch, Hugo. added "estimated" to the question.
I don't change answers. The reason is I don't want to mess with the history of what you've chosen, however I do leave a note in the discussion when I award back points.
Definitely something could be done to the testing engine, but it's not a high priority.
November 28, 2012 at 10:08 am
Well this is the first time that I have noticed the disconnect so that shows how important it is to me! 😀 Thanks for the insight Steve.
November 28, 2012 at 10:40 am
I guessed 9 because there were 9 statements and I couldn't find anything documented about a particular behavior of them in estimating a query plan? After plugging it into SSMS I see 8 queries. Interesting, but I don't know what I learned??? :unsure:
Aigle de Guerre!
November 28, 2012 at 3:23 pm
seankyleprice (11/28/2012)
I believe I maybe have an answer as to why the behaviour of SET QUOTED_IDENTIFIER ON is different to the other set options, namely that the statement is issued at parse time whereas the others are issued at execute or run time. If you think about it, this needs to happen for Intellisense to work as it does. The implication is that if the SET statement is present, the setting takes effect, regardless of whether code execution actually reaches that point; and the SET statement takes effect before any statements are executed. For example, even if the set statement is in an IF...ELSE statement block that is never reached during execution, the SET statement still takes effect because the IF...ELSE statement block is parsed. The following illustrates the point:SET QUOTED_IDENTIFIER OFF;
IF 1 = 2
BEGIN
SET QUOTED_IDENTIFIER ON
END;
select "'This will raise an error if OUOTED_IDENTIFIER is ON'" + "";
That's great, it accounts for the odd behaviour distinguishing that statement from the other 8, and your code example demonstrates very clearly that it's a correct explanation.
Tom
November 28, 2012 at 3:48 pm
L' Eomot Inversé (11/28/2012)
seankyleprice (11/28/2012)
I believe I maybe have an answer as to why the behaviour of SET QUOTED_IDENTIFIER ON is different to the other set options, namely that the statement is issued at parse time whereas the others are issued at execute or run time. If you think about it, this needs to happen for Intellisense to work as it does. The implication is that if the SET statement is present, the setting takes effect, regardless of whether code execution actually reaches that point; and the SET statement takes effect before any statements are executed. For example, even if the set statement is in an IF...ELSE statement block that is never reached during execution, the SET statement still takes effect because the IF...ELSE statement block is parsed. The following illustrates the point:SET QUOTED_IDENTIFIER OFF;
IF 1 = 2
BEGIN
SET QUOTED_IDENTIFIER ON
END;
select "'This will raise an error if OUOTED_IDENTIFIER is ON'" + "";
That's great, it accounts for the odd behaviour distinguishing that statement from the other 8, and your code example demonstrates very clearly that it's a correct explanation.
Thanks seankyleprice, this adds a lot to the value of the question.
November 28, 2012 at 4:08 pm
Just tried this out on a SQL 2012 instance, with SSMS 2012, and the behaviour is exactly the same. Eight statements in the estimated execution plan, and none in the actual execution plan.
To Dave62, just a thought, given all the negative feedback you had, maybe it would have been a better idea to just post this one to the 2008 forum and ask if anyone could explain this odd behaviour? Anyhow, it sparked a nice debate, and we all were reminded again to not trust the results of the estimated plan it comes up with.
Regards,
Jan
November 28, 2012 at 4:21 pm
davoscollective (11/28/2012)
L' Eomot Inversé (11/28/2012)
seankyleprice (11/28/2012)
I believe I maybe have an answer as to why the behaviour of SET QUOTED_IDENTIFIER ON is different to the other set options, namely that the statement is issued at parse time whereas the others are issued at execute or run time. If you think about it, this needs to happen for Intellisense to work as it does. The implication is that if the SET statement is present, the setting takes effect, regardless of whether code execution actually reaches that point; and the SET statement takes effect before any statements are executed. For example, even if the set statement is in an IF...ELSE statement block that is never reached during execution, the SET statement still takes effect because the IF...ELSE statement block is parsed. The following illustrates the point:SET QUOTED_IDENTIFIER OFF;
IF 1 = 2
BEGIN
SET QUOTED_IDENTIFIER ON
END;
select "'This will raise an error if OUOTED_IDENTIFIER is ON'" + "";
That's great, it accounts for the odd behaviour distinguishing that statement from the other 8, and your code example demonstrates very clearly that it's a correct explanation.
Thanks seankyleprice, this adds a lot to the value of the question.
Thanks. Interestingly (to me at least) we can also see that the parse phase of the query optimiser appears to be actually at least two distinct phases. Check out the following SQL:
SET QUOTED_IDENTIFIER ON
GO
hgdghg --Random rubbish that will not parse
SET QUOTED_IDENTIFIER OFF
GO
select "'This will raise an error if QUOTED_IDENTIFIER is ON'" + ""
GO
Even though the random gobbledygook should not parse the final query succeeds. From this I can only assume that it works something like as follows:
1. Extract only those statements that are issued at parse time;
2. Run the parse time statements;
3. Parse the rest of the statements.
And actually, that makes sense given that the parse time statements tend to impact how the rest of the statements should be parsed.
Viewing 15 posts - 31 through 45 (of 60 total)
You must be logged in to reply to this topic. Login to reply