Queries

  • Jan Van der Eecken

    SSCrazy Eights

    Points: 8890

    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

    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

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

  • Dave62

    SSCertifiable

    Points: 6535

    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!

  • JAZZ Master

    Hall of Fame

    Points: 3292

    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: 281243

    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: 3663

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

  • Andre Ranieri

    SSCrazy

    Points: 2819

    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: 717408

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

  • seatedElephant

    Say Hey Kid

    Points: 674

    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

    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

    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: 6535

    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

    got it wrong as i dint understand:(

  • shaunsisk

    SSC Rookie

    Points: 45

    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.

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

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