Order My Data

Question

Post reply

Order My Data

steve.jacobs

SSCommitted

Points: 1830
More actions
November 30, 2013 at 12:40 pm

#285634

Comments posted to this topic are about the item Order My Data
December 2, 2013 at 1:28 am

This was removed by the editor as SPAM

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

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

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 More actions · Answer 2

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: 22596 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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: 7422 More actions · Answer 6

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 SSCertifiable Points: 5617 More actions · Answer 7

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: 259085 More actions · Answer 8

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: 22596 More actions · Answer 9

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: 1706 More actions · Answer 10

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.

paul s-306273 SSChampion Points: 10727 More actions · Answer 11

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

Maybe we need better validation of QOTDs.

SQLDoubleG Hall of Fame Points: 3100 More actions · Answer 12

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: 10727 More actions · Answer 13

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.