Order My Data

Question

Order My Data

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

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

Rich Weissler Hall of Fame Points: 3286 More actions · Answer 1

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

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

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

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

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

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

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

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

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

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: 3100 More actions · Answer 11

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: 65141 More actions · Answer 12

nuff said 😛

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

TomThomson SSC Guru Points: 104773 More actions · Answer 13

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: 286589 More actions · Answer 14

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

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.