I recently modified a stored procedure at work and ended up removing a sort order and was reminded that Explicit Sort Order Matters.

The short version of the procedure is that it fills a temporary table with data, does some manipulation of that data and then returns a subset of the data. The problem was, the data contains an NTEXT column which contains XML data. Before you ask, the system was architected before SQL 2005 and the XML choice wasn't available. After the database was moved to a 2005 server, there was no need to change the data to XML because the database doesn't actually care about the data, it handles information used by the client.

NTEXT columns can be expensive, even in a temporary table, and especially when you've got several thousand rows to be slinging about and the average data length in the NTEXT is a tad under 1K. So, to optimize performance, I removed the NTEXT from the temporary table and added it in using a JOIN at the end of the procedure.

select *, Row_Number() OVER(ORDER BY DateUpdated Desc) RowNumber FROM #HoldingTable) A
inner join content c on a.indexguid = c.indexguid
where RowNumber between @LowRank  and @HighRank

Before I added the JOIN, everything worked. After I added the JOIN, it mostly worked, but there were some strange items that weren't sorted correctly. That's because the JOIN introduced some optimization issues that threw off the sort order. So I added ORDER BY RowNumber and everything works.