Microsoft is often considered a leader, an innovator, a trend-setter. The same could be said for Apple, Google, and a host of other tech companies. And each of those has its set of critics as well, who think that the company is the opposite – or worse. Some people think it is a good idea to model their own code, architecture or applications after things that these companies have done, but this is not always the best approach. Humans work at these companies too, and everyone is prone to mistakes, even when there are 40 sets of eyes that review a piece of code before it ships.
I am a consumer of many vendors' products, but Microsoft's quite easily are the ones with which I am most familiar. Even being an MVP, I don't have access to all of the source code for all of the products, but I do have a tendency to tinker with SQL Server stuff. Again, not source code, per se, but definitely a lot of the objects that get created in SQL Server system databases.
In a recent private discussion with some MVPs, we were talking about sp_who2 – how it shows 'RUNNABLE' for the spid that called it, even though that is clearly not accurate. When I was looking through the code for sp_who2, I noticed the final SELECT (against a #temp table) had the following ORDER BY, but commented out:
-- (Seems always auto sorted.) order by spid_sort
Implying that commenting out the ORDER BY has no impact on the order of the results. This is a terrible, terrible, terrible myth to perpetuate. As many SQL Server experts (and many folks within Microsoft) will tell you: if you, your users, or your applications are assuming and expecting a certain sort order, use an ORDER BY clause. In this case, the word "Seems" should be a clue. What is the point of commenting out this ORDER BY clause? All this does is open the door to unexpected behavior should the code above it change (or should the optimizer behave differently in the future, which can happen for a variety of reasons). Once I pointed this out to a couple of Microsoft folks, there's a good chance this will be fixed in the Denali time frame. But anyone looking at the code in the meantime will just have further "proof" that the myth is real.
Just because you observe a certain order when you run the query once (or a hundred times) does not mean that that ordering is guaranteed. Trust me, this will burn you eventually if you keep relying on it! In my opinion, you should ALWAYS, ALWAYS, ALWAYS use an ORDER BY clause from the get-go, even if you don't care how the data is ordered. Without it, certain behaviors will be assumed, and if you later add ordering or the native (undefined!) ordering changes for whatever reason, you'll be fixing it somehow. If you want the ordering to be random, then do that: ORDER BY NEWID() or some similar construct. If you want the ordering to follow the clustered index, then say so: ORDER BY <clustered index column(s)>.
If you still think that relying on undefined ordering is okay, please go back and read my bad habit post about ORDER BY in a view, and also see Dave Ballantyne's blog post where he reminds us that Without "ORDER BY", order is not guaranteed. Also Alex Kuznetsov has two great and to-the-point posts about this: Without ORDER BY, there is no default sort order and Uncertainty erodes confidence, so add that ORDER BY clause.