Uncertainty erodes confidence, so add that ORDER BY clause.

I was reading Eric Lippert's blog post, and came across the following quote: "uncertainty erodes confidence in our users that we have a quality product that was designed with deep thought, implemented with care, and behaves predictably and sensibly".

How very true.

By default, the code we are developing should run in exactly the same way every time it is invoked - this is what our customers usually want. For example, in my experience every select should have an ORDER BY clause, and that clause should uniquely identify the order.

Of course, when we are determining the requirements, we always need to ask the customers how to sort the results. Suppose, however, that they tell us that they don't care about the ordering of the result set. Should we skip the ORDER BY clause and potentially save some CPU cycles on sorting?

In most cases, I would not do that, and the reason is simple: even if we explicitly say in the documentation that the sort order of the results is not guaranteed, eventually someone will rely on that order. If, no, not if, when the sort order changes, that will break something. Somebody will complain that their code is broken. Surely we can educate our customers that Without ORDER BY, there is no default sort order

If we explain it well, they will surely understand it - they are at least as smart as we are. Yet they still may sometimes rely on that sort order, because apparently the whole concept of unordered result sets is counter-intuitive to a lot of very smart people.

So, if we do not add an ORDER BY clause in our store procedure, later on we may have to return to that procedure and add ORDER BY, regardless of what our customers are saying right now. In fact, in most cases our customers do want our code to run in exactly the same way every time it is invoked, unless they explicitly request random behavior.

Let us save everybody's time and by default add an ORDER BY clause every time we develop a select that delivers results to the client, unless there is an explicit requirement not to do so.

Comment Notification

Comments

So results should not change order, except if you are a SQL Server developer, in this case it's not your responsability if someone else relies on the order a particular operator returns and that order change with a service pack or hotfix...

Sometimes developers outside MS have reasons to avoid an "ORDER BY" wich happens to be the same as SQL Server developers change the order of some operator from time to time, an "ORDER BY" on a big set will first save all records and them cause a slow sortontempdb just to give a more predicable to the guy who is streaming those billions records and don't care about the order at all...

It is my responsibility to determine what my customers want, and act on it. You are mentioning a good example, when ORDER BY in views stopped working in 2005. On one hand, the users should read the manual. On the other hand, do you really want to spend your precious time explaining that over and over again? Or do you have other things to do?

It is not always correct that "an "ORDER BY" on a big set will first save all records and them cause a slow sortontempdb". Still, ordering may slow things down, no argument here. However, typically changing sort order should not come as a surprise, it should not an optimizer's decision. In many cases we should develop another faster method, and ask customers to switch if they want better performance. Of course, this is not a blanket rule.

@ALZDBA,

I know that it is common practice to roll out V1 that behaves irrationally, then fix it later. I don't think my customers would appreciate it.

Very good advice. I'm sitting here looking at a request in our change request system asking for "some rhyme or reason as to how results are sorted" because "without some kind of 'sort' you have no idea where in the list your application will be found"

Now this two minute change is going to go through a bunch of work groups to get prioritized at great expense when it could have had a default sort from the beginning.

October 19, 2010 10:01 AM

Leave a Comment

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization.
Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.