Order My Data

  • steve.jacobs

    SSCommitted

    Points: 1830

    Comments posted to this topic are about the item Order My Data

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    All options are true except this one

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

    You should correct your question.

    Igor Micev,
    My blog: www.igormicev.com

  • steve.jacobs

    SSCommitted

    Points: 1830

    IgorMi (12/1/2013)


    All options are true except this one

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

    You should correct your question.

    Igor, you are correct and thank you for pointing it out. If you were to combine the index with the Order By, then column a would be sorted and the only option that would not "work" would be the one you noted. I would like to correct the question in stating "if you had the option to add only one and not multiples..." such as index and ORDER BY. The intent of the question is adding only one value from the list to order column 'a' not multiples. For example:

    create table #temp1

    (

    i int identity(1,1),

    a numeric(8,2) default '0'

    )

    CREATE CLUSTERED INDEX IDX_A ON #temp1(a)

    insert into #temp1 (a)

    select 1

    union all

    select '32'

    union all

    select 21

    union all

    select null

    union all

    select '0'

    union all

    select .5

    union all

    select NULL

    union all

    select '.5'

    union all

    select '32'

    select i, cast((cast(#temp1.a as float) + 10) as varchar) from #temp1

    drop table #temp1

    Administers, how do I go about "fixing" the question once it is published?

    To all that misunderstood the intent and the inadequate question, my apologies:ermm:

  • Carlo Romagnano

    SSC-Insane

    Points: 21830

    steve.jacobs (11/30/2013)


    Comments posted to this topic are about the item <A HREF="/questions/T-SQL/104494/">Order My Data</A>

    OhiOhiOhi.

    The only right answer is add a "order by clause" to the select statement.

    Without the order by you are not granted about the order of the data.

    It depends on cpu load, memory, concurrency, parallel processing.

    You can run your test and have the same result, but in the production environment you may have unexpected order.

  • karyn.webb

    SSChasing Mays

    Points: 618

    That was my understanding as well. I only selected the one answer. And I did read that I should select 4. But I'm pretty sure that Hugo has had strong words in the past about not relying on the indexes to order data.

    And now I'm thinking that I made that all up.

  • Mr. Kapsicum

    SSCertifiable

    Points: 6128

    Carlo Romagnano (12/1/2013)


    steve.jacobs (11/30/2013)


    Comments posted to this topic are about the item <A HREF="/questions/T-SQL/104494/">Order My Data</A>

    OhiOhiOhi.

    The only right answer is add a "order by clause" to the select statement.

    Without the order by you are not granted about the order of the data.

    It depends on cpu load, memory, concurrency, parallel processing.

    You can run your test and have the same result, but in the production environment you may have unexpected order.

    +1.

    The Only way you can guarantee ORDERED result set, is by adding ORDER BY in the Statement.

    (Even Though This Question has Very Less Data, it will order the result by INDEXING, But in PRACTICAL SCENARIOS it will not work) 😉

  • Evgeny Garaev

    SSCertifiable

    Points: 6323

    What is wrong with this answer?

    CREATE NONCLUSTERED INDEX IDX_A ON #temp1(a)

    Even though I agree that only order by guarantee the order...

  • palotaiarpad

    SSCarpal Tunnel

    Points: 4891

    If i need a specific order i'll use ORDER BY. It's the clearest method to avoid future problems. 😎

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    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 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Carlo Romagnano

    SSC-Insane

    Points: 21830

    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.

  • Mikael Eriksson SE

    SSCommitted

    Points: 1688

    There is only one correct answer to this question.

    You always need an order by on the select statement if you want the rows ordered in the resultset.

    Sometime they may be ordered even if the order by clause is missing but they might as well not be. You just can not be sure about the order without order by.

  • This was removed by the editor as SPAM

  • paul s-306273

    SSChampion

    Points: 10602

    Not sure about this question - I answered but got it wrong...

    Maybe we need better validation of QOTDs.

  • SQLDoubleG

    Hall of Fame

    Points: 3090

    I guess this thread will grow really big.

    I agree with some other peers, (and with BOL), there is just one possible answer for this question, which is include ORDER BY in the select statement

    http://technet.microsoft.com/en-us/library/ms188385.aspx

    The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified

  • paul s-306273

    SSChampion

    Points: 10602

    raulggonzalez (12/2/2013)


    I guess this thread will grow really big.

    I agree with some other peers, (and with BOL), there is just one possible answer for this question, which is include ORDER BY in the select statement

    http://technet.microsoft.com/en-us/library/ms188385.aspx

    The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified

    My (and I guess others) original reaction.

Viewing 15 posts - 1 through 15 (of 44 total)

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