Order My Data

  • Rich Weissler

    Hall of Fame

    Points: 3235

    *sigh* Add my voice to the multitude who reiterate definitively that only the ORDER BY guarantees achieving the desired result. :ermm: Tossing indexes on the table hoping the output will change simply draws forth a firestorm.

  • Toreador

    SSChampion

    Points: 11231

    Sadly all those who simply read the explanation and never visit the discussion forum will now go away with false beliefs, and QotD will have worsened understanding rather than increasing it.

  • RichB

    SSCrazy Eights

    Points: 9651

    Add my voice to the concerns on this one...

    as well as a +1 to Toreador for that comment.

  • Victor Kirkpatrick

    Hall of Fame

    Points: 3672

    This question should be deleted before people go away with a very very very bad assumption: I can throw a clustered index on a column and always expect it to be sorted this way.

    There is just one correct answer to this question: use the ORDER BY statement. The only guarantee, period.

  • ronmoses@gmail.com

    SSCarpal Tunnel

    Points: 4480

    CREATE UNIQUE CLUSTERED INDEX IDX_A ON #temp1(a,i)

    Did I miss the part that said the combination of a and i will be unique? It is in the given dataset, but clearly the assumptions are referencing future usage as it says a will allow duplicates. Unless it's stated specifically, I don't think it's safe to assume the combination of these columns will be unique.

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • steve.jacobs

    SSCommitted

    Points: 1830

    All,

    Again, my apologies for neglecting to be more clear when asking the question. From Igor's initial comment to this question, I immediately knew I messed up and was going to get "ripped" and I deserve it. Here is my reply to Igor's comment (page 1, 3rd post)).

    http://www.sqlservercentral.com/Forums/Topic1518661-3459-1.aspx.

    All, this will NEVER happen again.

  • Luis Cazares

    SSC Guru

    Points: 183568

    Victor Kirkpatrick (12/2/2013)


    This question should be deleted before people go away with a very very very bad assumption: I can throw a clustered index on a column and always expect it to be sorted this way.

    There is just one correct answer to this question: use the ORDER BY statement. The only guarantee, period.

    The combination of the columns should be unique as i is an identity column and will generate different values each time. Unless, of course, that someone messes with it with SET IDENTITY_INSERT ON or DBCC CHECKIDENT.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Carlo Romagnano (12/2/2013)


    Koen Verbeeck (12/2/2013)


    Evgeny (12/1/2013)


    What is wrong with this answer?

    CREATE NONCLUSTERED INDEX IDX_A ON #temp1(a)

    +1

    Why is this answer "incorrect"?

    Flawed question today, better luck next time 🙂

    It's wrong because the optimizer never use NONCLUSTERED index, if it do not need it.

    Hi Carlo,

    Since the following query "Select a from #temp1" is in question, the optimizer will exactly use the above mentioned non-clustered index. Even under presence of a clustered index which cannot be only on "a", but must include e unique column - in this case "i" (with identity property), the optimizer will still choose the non-clustered index. Please try that to ensure yourself.

    Regards,

    IgorMi

    Igor Micev,
    My blog: www.igormicev.com

  • Mighty

    SSCrazy Eights

    Points: 8452

    Guess that all has been said before. Only the "order by" answer is correct.

    Agree with some others to delete this question, before people might get a wrong idea.

  • ronmoses@gmail.com

    SSCarpal Tunnel

    Points: 4480

    Luis Cazares (12/2/2013)


    The combination of the columns should be unique as i is an identity column and will generate different values each time.

    Blast it, I read this whole thing wrong. Thanks for setting me straight.

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • SQLDoubleG

    Hall of Fame

    Points: 3090

    steve.jacobs (12/2/2013)


    All,

    Again, my apologies for neglecting to be more clear when asking the question. From Igor's initial comment to this question, I immediately knew I messed up and was going to get "ripped" and I deserve it. Here is my reply to Igor's comment (page 1, 3rd post)).

    http://www.sqlservercentral.com/Forums/Topic1518661-3459-1.aspx.

    All, this will NEVER happen again.

    Obviously who never writes a QotD will never make a mess.

    So although what's been talked, hopefully will not discourage you to keep writing QotD's.

    Thanks for the question anyway.

    PD. Also I'd love authors have a "preview" when writing QotD's :Whistling:

  • David Burrows

    SSC Guru

    Points: 64521

    nuff said 😛

    Far away is close at hand in the images of elsewhere.
    Anon.

  • TomThomson

    SSC Guru

    Points: 104772

    I agree with everyone else who said the only safe way to do it is with an ORDER BY clause.

    As for unsafe methods, whichreally ought not to be used (or ought only to be used to speed up the execution of the order by clause) there are 4; so assuming we choose the safe method as one of our 4 answers, we are left to guess which three of the unsafe methods we should use.

    All in all a thoroughly unsatisfactory question. But the author shouldn't worry too much; others (including me) write bad questions. Steve (Jones) is pretty good at rapidly applying corrections that the question's author sends him.

    Tom

  • Sean Lange

    SSC Guru

    Points: 286446

    Here is a great article that explains the non safe approaches to ordering results.

    http://blogs.msdn.com/b/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • rmunwin

    SSC Journeyman

    Points: 76

    Yes it will if the non cluster covers the query which in the case of select a from #temp order by a will do as it can scan the index which is ordered.

Viewing 15 posts - 16 through 30 (of 44 total)

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