The title says it all. In a SQL Query, you can’t depend on the order that rows are returned without using the ORDER BY clause.
Years ago, this was one of the first lessons I learned about SQL. Without using ORDER BY I was assuming a particular order for returned rows and things went fine for a while. Then it simply “broke”: the results came back in an unexpected order. We quickly fixed the problem, but the client wanted to know what happened. They asked “What changed?” and the best answer that I could come up with is that our luck ran out. We were never entitled to assume an order to that particular set of results.
That’s all I wanted to say. Hmm… That makes for a short blog post eh?
It bears repeating and so why not. I’m going to re-explain myself. But this time I’ll do it as other bloggers you may or may not follow.
SQL Blog Impressions:
As Brent Ozar
The other day when I got back from walking my dog, I opened my Surface (my Surface Pro, not my Surface RT) and surfed the forums. Someone was wondering about the order of query results that didn’t use an ORDER BY clause. BWAAAAH! You can’t do that. After I cleaned the coffee off my monitor, I replied tactfully that you can’t depend on the order here. If you need to order your results, you need to use the ORDER BY clause. Better yet, sort the records in the app. App server CPU is way cheaper than SQL Server CPU. Sign up here for next Tuesday’s webcast about this very topic.
[Ed: It was inevitable, Brent tackles that question here]
As Pinal Dave
Kind sir or madam, allow me to welcome you to my humble website where I blog about my SQL Server journey and share with you what I’ve learned. You searched the web for SQL Server answers and clicked on the first link. That’s what brought you here and it’s my sincerest wish that I can help you out with your problem today.
Today I’m exploring the ordering of results when the ORDER BY clause is not used. Let us see what happens:
<One succinct explanation and example later…>
In conclusion, it is a mistake to believe that there is an order to results when the ORDER BY clause is not used.
[Ed: Pinal Dave is so prolific, I shouldn’t have been surprised to find out that he has in fact blogged before on this very topic!]
As Aaron Bertrand
The ANSI-SQL standard specifies ORDER BY as the only way to sort rows in a query. Without the ORDER BY clause, the rows may be returned in any way that SQL Server sees fit. It’s such an important fact, that I believe Microsoft should dedicate at least 50 per cent of the SQL Server Management Studio splash screen for the purpose of warning you. I’ve created a connect item for it so please go and vote.
Excuse me… I have to go, Microsoft just released a new cumulative update for SQL Server.
As Paul White
Examine a query without an ORDER BY clause; the order of the resulting rows is non-deterministic even though it may seem otherwise. We can tell because the input tree for a query with an ORDER BY clause contains the logical operators LogOp_OrderByCOL or LogOp_OrderByQCOL. The optimizer will then take steps to ensure the correct sort order. It will take advantage of existing indexes. Alternatively, the optimizer can choose to use a sort operator if needed.
Without the ORDER BY statement, the optimizer won’t enforce the order and so the order of returned rows can not be determined. It’s unwise to look at the execution plan and make a guess, your query plan could change at any time. And on top of that, the query optimizer algorithms can change at any version, SP or CU.
As Karen Lopez
Yesterday afternoon I was tweeting about data, Barbies and astronauts, and data about Barbie astronauts. It occurred to me that I really should be focusing on the conference session I was at. Especially since I was only half way through presenting it.
I was at my favourite conference centre and an attendee had just asked whether queries without ORDER BY clauses came back ordered by the Primary Key or the Clustered Key? It was an odd question to get during a talk about data modelling. I explained that the answer is neither: Without an ORDER BY clause, no order is guaranteed. Love your data people! And the order it comes in!
Hey, if you’re in Toronto next month
…
could you feed my cats?
As Paul Randal
Last week I asked you whether you could depend on the results of a query without an ORDER BY clause.
I’m encouraged that the “No” answer was most prevalent because that is the correct answer. It’s not data corruption, it’s the way that SQL Server works and it has since at least SQL Server 2005 when I worked on the team. So remember the ORDER BY clause if you need to depend on the order (and don’t forget to run DBCC CHECKDB afterwards).
HAHAHA, nice, sir. I’ve wanted to do this FOREVER, but without the names attached, and have people guess the names. I think it’s awesome that bloggers are developing their natural voice, things that work well for them, and that they’re recognizable.
Nice job, man.
Comment by Brent Ozar — September 13, 2013 @ 10:30 am
OMG this is so good.
Comment by Matt Velic — September 13, 2013 @ 10:50 am
Thanks Brent, Who would have been in your list that’s not above? I regret not doing Tom Larock and John Sansom. They’re both recognizable almost solely by excellence and that’s hard to parody.
Comment by Michael J. Swart — September 13, 2013 @ 10:52 am
Excellent! 🙂
Comment by Paul Randal — September 13, 2013 @ 10:55 am
You got it – I was just saying in our company chat room that I would have added LaRock, and Sansom’s a great addition too. One other one you missed: Books Online, heh.
There’s a lot of other great prolific writers in the community, but they haven’t settled into a specific voice. Steve Jones has a really good “voice” on his videos, a personal and casual style that I like a lot, but it’s harder to tell with his writing. Don’t get me wrong, I enjoy his writing too, but it’s harder to pick out of a crowd because he mixes things up.
Comment by Brent Ozar — September 13, 2013 @ 10:57 am
You could do Jeremiah – it’s real short:
“To order, or not to order? I’ll start: ORDER BY! Up yours.”
😉
Comment by Allen McGuire — September 13, 2013 @ 11:18 am
Genius. Thanks.
Comment by David Wimbush — September 13, 2013 @ 11:20 am
Also didn’t make the cut:
This post is the 10,347th part of a ramble-rant about the software business….
:{>
Comment by Michael J. Swart — September 13, 2013 @ 11:29 am
“Stairway to Order By” – by Neil Armstrong (one small step for DBAs, one giant leap for developers?)
Comment by Allen McGuire — September 13, 2013 @ 11:39 am
Stairway series… forgot about those, well done Allen!
Comment by Michael J. Swart — September 13, 2013 @ 11:43 am
Michael, this is pure genius. Genius I tell ya. You’ve nailed each one perfectly. Lampooned even. Thank you for making my dreary Friday into a manic Monday. Maniacal laughter, that is.
Comment by Steven Ormrod — September 13, 2013 @ 11:53 am
Brilliant!!!
Comment by tjaybelt — September 13, 2013 @ 11:59 am
Ordering in the database is a degenerate behavior. Sort order is purely a matter of application performance and can easily be extended through multi-process parallel algorithms in the server layer.
Database sorts are also inherently broken. Please see David Koelle’s excellent AlphaNum sorting algorithm which takes into account the way that humans mentally perceive data: http://www.davekoelle.com/alphanum.html
Also ..|.. ..|..
Comment by Jeremiah Peschka — September 13, 2013 @ 12:12 pm
Classic post, sir. I love it. Thanks for the big grin and chuckles while reading this one, Michael.
Comment by Mike Walsh — September 13, 2013 @ 12:16 pm
That’s the Jeremiah I know. (Uncanny)
Comment by Michael J. Swart — September 13, 2013 @ 12:48 pm
Brilliant! F_king brilliant!
Thank you :-).
Comment by Marian — September 13, 2013 @ 12:54 pm
Stack Overflow:
Q: How can I guarantee the results of the query below are ordered?
SELECT *
FROM Person.Contact
A1: You can’t.
A2: The better question is, why would you want to?
A3: Never mind, I figured it out.
Comment by Dave Swart — September 13, 2013 @ 2:41 pm
I laughed so hard, I think I tore a meniscus or something like that. =^D
Comment by Kevin Kline — September 13, 2013 @ 3:08 pm
The StackOverflow one is spot on.
Comment by Steven Ormrod — September 13, 2013 @ 3:30 pm
Thanks Kevin!
Regarding the stack overflow parody:
Ladies and Gentlemen, My brother, David Swart.
Comment by Michael J. Swart — September 13, 2013 @ 4:28 pm
Just passed on this nugget of knowledge last week (again).
Comment by Jeff Cheney — September 13, 2013 @ 6:56 pm
Can’t stop laughing, everythin is so true, I read each and one of these blogs on a weekly basis and you have nailed it. As for StackOverflow, I just updated one of my answers with a link to this post, it can’t pass unseen. http://stackoverflow.com/a/16384021/1297603
Comment by Yaroslav — September 14, 2013 @ 3:45 am
I follow all those bloggers and you really killed it. At first I was only half paying attention and though it was excerpts from the actual blogs. I was scratching my head why Brent would dedicate a webcast to the topic and actually clicked the link. Thanks for the laughs today!
Comment by Jeff York — September 16, 2013 @ 11:27 am
@Jeff York
Mwaahahaha…
Excellent
Comment by Michael J. Swart — September 16, 2013 @ 2:02 pm
Nice post, one of the most debatable T-SQL items.
I also have this blogged few months before on my personal blog: http://sqlwithmanoj.wordpress.com/2013/06/02/clustered-index-do-not-guarantee-physically-ordering-or-sorting-of-rows/
Comment by Manoj Pandey — September 17, 2013 @ 5:38 am
This is great! (Can you provide a rag so folks can clean off their monitors?)
Comment by Wayne Sheffield — September 20, 2013 @ 6:46 pm
How could you not include Joe “Ming” Celko?
He would certainly address the relation calculus, the ANSI 92 standard, Set Based Operations, the effective use of check constraints, and the characteristics of the proper natural key.
Comment by Ray Herring — October 30, 2013 @ 4:54 pm
I’m sure I could have Ray.
(Ming? I had to google that. It’s a reference to a Flash Gordon character, Ming the Merciless. Man that’s obscure, good job.)
Comment by Michael J. Swart — October 31, 2013 @ 11:56 am
[…] As many people will point out, you can’t depend on the order of results without using the ORDER BY clause. So it’s easy to say “Simple! Don’t write code that expects unsorted data to be sorted”. But it’s very difficult to be careful everywhere all the time. […]
Pingback by You’re Probably Taking Sort Order For Granted Somewhere | Michael J. Swart — February 3, 2016 @ 8:01 am