There is a really good reason to be wary of using order by 1; it makes the order of your results prone to change should you modify the number or order of fields in your select list, but of course everyone knows that. However; what people don't always appreciate is the impact it can have on query performance.
I recently had the need to parse a large number (about 1.5 million per week in a batch process) of text strings and extract certain elements. The elements could be in any position in the text string, there could be one or many occurrences of them in each string, and all had to be extracted. The solution also had to be easily configurable to allow for additional elements being added at a later date.
I can't go in to any real detail about the solution I chose, as that is intellectual property of my employer, however, it is likely that I will discuss some of the techniques employed in future posts.
When developing the proof of concept, I ordered the output by the raw text string so that so that I could confirm that all required elements had been correctly identified. Once happy with the code I ran the full 1.5 million records through, which took about 3 minutes. The last stage was to pull through a unique identifier which was stored with the text string. Incredibly the processing time increased 5 fold.
After satisfying myself that the query times were reproducible, and not a consequence of other activity on the server, I began comparing the execution plans. There appeared to be only one difference; the longer running query contained a sort step that wasn't present in the original.
It appears that the method I used implicitly ordered the result set by the text string, and so in the original query there was no impact on the query plan. When I added the unique identifier to the start of the select list it forced a reordering of that data, and incurred a performance hit. In this case the performance degradation was exacerbated by the fact the parsing process multiplied the in-process data by a factor of around 100.
Conclusions
Whilst the method I employed meant that the performance impact of the unexpected ordering was extreme, it does demonstrate that the effect of unexpected ordering cannot be ignored. Do I expect people to stop using order by 1? No, I wont even promise that I will. All I hope is that people will do so with a conscious awareness of the potential consequences.