T-SQL

  • We tend to avoid ORDER BY clauses in stored procs because we let the application do the sorting. A lot of times the app can sort it faster. Thus the execution plan does not include a sort, and since the proc is parameterized, there's no telling how much data SQL would have to sort before returning anything.

    Tony
    ------------------------------------
    Are you suggesting coconuts migrate?

  • John Mitchell-245523 (7/22/2013)


    kapil_kk (7/22/2013)


    Yes you are right but user PRR asked a scenario only for clustered index and if a clustered index is used then it will reorder of the data in physical order...

    You're confusing the way the data is stored with the way it's returned. As Hugo explained, they're not guaranteed to be the same.

    John

    Edit - corrected typo.

    Actually it is guaranteed neither that the way the data is stored (the physical storage order) nor the order in which the rows are returned (a property of the optimiser's query plan including any effects of parallel execution, advanced scans, existence of other indexes that are "good enough", parallel scanning, use of advanced scan, etc etc) is the clustered index order; all that's guaranteed is that the logical storage order (which can be quite different from the physical storage order) matches the clustered index order.

    And it's terrifying that more than a third of people get this wrong, because that suggests that there is rather a lot of SQL out there that works only by accident and is just waiting for a small change (adding non-clustered index that covers query, or upgrading the server, or upgrading the storage, or growing the amount of data, or .....) to break and deliver stuff in an order that causes the application to do something wrong.

    Tom

  • Nice and easy relational theory question.

    Can not even begin to count the times were this basic piece of relational set logic has not been understood.

    :w00t:

  • L' Eomot Inversé (7/22/2013)

    And it's terrifying that more than a third of people get this wrong, because that suggests that there is rather a lot of SQL out there that works only by accident

    It also shows that there are an awful lot of people who answer the QotD but then never bother reading the associated threads, as this behaviour is mentioned regularly. Which in itself is worrying, given the numbers of questions over the years that have had incorrect/misleading answers which are then resolved in the ensuing discussion...

  • L' Eomot Inversé (7/22/2013)


    John Mitchell-245523 (7/22/2013)


    kapil_kk (7/22/2013)


    Yes you are right but user PRR asked a scenario only for clustered index and if a clustered index is used then it will reorder of the data in physical order...

    You're confusing the way the data is stored with the way it's returned. As Hugo explained, they're not guaranteed to be the same.

    John

    Edit - corrected typo.

    Actually it is guaranteed neither that the way the data is stored (the physical storage order) nor the order in which the rows are returned (a property of the optimiser's query plan including any effects of parallel execution, advanced scans, existence of other indexes that are "good enough", parallel scanning, use of advanced scan, etc etc) is the clustered index order; all that's guaranteed is that the logical storage order (which can be quite different from the physical storage order) matches the clustered index order.

    Yes, what you say is quite right. I was using the word "way" in its most general sense, to include logical as well as physical order. I didn't point out the distinction between logical and physical to Kapil since the data isn't guaranteed to be returned in either order.

    John

  • bitbucket-25253 (7/20/2013)


    If any one misses this basic question ... woe to the place where they work

    +1

    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

  • SQLRNNR (7/25/2013)


    bitbucket-25253 (7/20/2013)


    If any one misses this basic question ... woe to the place where they work

    +1

    +1

    Not all gray hairs are Dinosaurs!

  • This is my first interview question for anyone who will be working with SQL. I usually "mix it up" a bit by stating that the table has a clustered PK, several other indexes, etc. and then ask about various simple SELECT statements. Probably 9 out of 10 candidates give an incorrect answer. When asked to explain the reason behind their answer, their explanations are very interesting. What is most disturbing is that when I ask "would you bet your life on it?" most will say "yes" to their incorrect answer.

    Needless, to say, the interview process is cut short. "Next..."


    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • Miles Neale (7/26/2013)


    SQLRNNR (7/25/2013)


    bitbucket-25253 (7/20/2013)


    If any one misses this basic question ... woe to the place where they work

    +1

    +1

    +1 once more... 🙂

  • sqlnaive (8/1/2013)


    Miles Neale (7/26/2013)


    SQLRNNR (7/25/2013)


    bitbucket-25253 (7/20/2013)


    If any one misses this basic question ... woe to the place where they work

    +1

    +1

    +1 once more... 🙂

    This is why we need a "+1" or "like" signal that doesn't clutter-up the thread. 🙂

  • Mike Dougherty-384281 (8/1/2013)


    This is why we need a "+1" or "like" signal that doesn't clutter-up the thread. 🙂

    +1!

    (oops)

  • I think the bottom line is that SQL Optimizer will return rows in the best order it sees fit based on execution plan. There are definitely cases, especially with larger tables using parallelism, when even the order of the clustered index is not obeyed/returned to the calling client.....

  • hesitated for a moment thinking this was a trick question 😉

Viewing 13 posts - 31 through 42 (of 42 total)

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