Matthew McGiffen notes that there is only one way to order, and that is to use the ORDER BY clause:
Everyone, at the beginning of their SQL career, get’s told that it is important to include an ORDER BY if they want the results ordered. Otherwise the order in which they are returned is not guaranteed.
But then you run queries a lot of times that don’t need a specific order – and you see that they (at least seem to) come out in the same order every time. You could (almost) be forgiven for thinking you can rely on that.
There was even a question on a Microsoft SQL certification exam a few years ago that asked what the default order was for records returned by a simple SELECT – the answer it was looking for was that it would be according to the order of the clustered index. So you can rely on that – right?
Wrong. The question was a bad question, and the answer was incorrect. Let’s look at this in action.
Order is never guaranteed to be stable unless you specify a unique ordering using ORDER BY.