Queries

Question

Queries

Viewing 15 posts - 46 through 60 (of 60 total)

You must be logged in to reply to this topic. Login to reply

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 1

seankyleprice (11/28/2012)
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.

Remember, the parser and the query optimizer are two very distinct animals.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

seankyleprice SSC Veteran Points: 214 More actions · Answer 2

Jan Van der Eecken (11/28/2012)

Remember, the parser and the query optimizer are two very distinct animals.

Thanks Jan. You are absolutely correct. I should have said query processor.

Sean

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 3

Great discussion on this topic and question. Had no clue myself.

Dave62 SSCertifiable Points: 7459 More actions · Answer 4

Jan Van der Eecken (11/28/2012)
...
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? ...

Thanks for the suggestion Jan but when I came across this behavior, I thought it was interesting and did not really have a question I needed answered in on of the forums. Along with the negative feedback there have also been a few others who found it interesting as I did. So I think I'll just continue to share things here if I find them interesting and enjoy the discussion that ensues.

After all, if I try to post something that not one person would find something to complain about then I'd most likely end up not posting anything. :rolleyes:

Enjoy!

Julie Hall of Fame Points: 3292 More actions · Answer 5

Dave62 (11/29/2012)
Jan Van der Eecken (11/28/2012)
...
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? ...
Thanks for the suggestion Jan but when I came across this behavior, I thought it was interesting and did not really have a question I needed answered in on of the forums. Along with the negative feedback there have also been a few others who found it interesting as I did. So I think I'll just continue to share things here if I find them interesting and enjoy the discussion that ensues.
After all, if I try to post something that not one person would find something to complain about then I'd most likely end up not posting anything. :rolleyes:
Enjoy!

+1

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Thanks for the question

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

Tee Time Hall of Fame Points: 3745 More actions · Answer 7

Tee Time

Hall of Fame

Points: 3745

December 3, 2012 at 9:32 am

#1564947

Choose 0 and get it wrong, what a winner!! 😛

Andre Ranieri SSCrazy Points: 2819 More actions · Answer 8

I also selected 0 for this answer. None of the statements returned any data. They actual execution plan confirms this.

Cheers,

Andre Ranieri

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 9

A query has nothing to do with returning data. Plenty of queries are valid but return no data.

seatedElephant Say Hey Kid Points: 674 More actions · Answer 10

Not being sure how many rows would be affected by those statements, I tested with SQL 2008. It worked for me but not sure what I've learnt.

Dave Morris :alien:

"Measure twice, saw once"

SanDroid SSChampion Points: 10068 More actions · Answer 11

Wow... lots of posts about this.

I understandthe Author wanted us to realize that no matter what your TSQL is, when you display an execution plan at least one Row has to be selected from the Plan cache to display it.

Could have been worded better though.

If more that 80% don't get your question right, you should reconsider what you are asking and how you re asking it. 😉

okbangas SSChampion Points: 11773 More actions · Answer 12

SanDroid (1/18/2013)
If more that 80% don't get your question right, you should reconsider what you are asking and how you re asking it. 😉

I completely disagree. There are quite a lot if tricky questions that can be asked, that are relevant and may be answered wrong by a lot of people. The clue about these questions is to learn, not that most of the answers shall be correct.

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

Dave62 SSCertifiable Points: 7459 More actions · Answer 13

SanDroid (1/18/2013)
... If more that 80% don't get your question right, you should reconsider what you are asking and how you re asking it. 😉

That sounds logical. But of course one doesn't have this knowledge (about 80% don't get the question) until after the question has already been asked... :rolleyes:

Enjoy!

kalyani.k478 Default port Points: 1400 More actions · Answer 14

kalyani.k478

Default port

Points: 1400

January 20, 2013 at 10:06 pm

#1579080

got it wrong as i dint understand:(

shaunsisk SSC Rookie Points: 49 More actions · Answer 15

The answer for this is actually 0. if you run the estimated execution plan you get a result of 1 row referring to the execution plan results itself. you can follow that logic by running sql profiler and looking for the rows affected.

If you were to run the actual execution plan on this while executing you get 0 rows.