Order My Data

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

  • 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

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

  • 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.

  • 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.

  • 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) 😉

  • 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...

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

  • 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

  • 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.

  • 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

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

    Maybe we need better validation of QOTDs.

  • 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

  • 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