When you write a query to return the first few rows from a potential result set, you’ll often use the TOP clause. To give a precise meaning to the TOP operation, it will normally be accompanied by an ORDER BY clause. Together, the TOP…ORDER BY construction can be used to precisely identify which top ‘n’ rows should be returned.
The ‘Top N’ Sort
Thinking about how this requirement might be implemented in an executable query plan, we might expect to see a Sort iterator followed by a Top. In reality, the query optimizer can often collapse these two related operations into a single iterator: a Sort iterator running in Top N Sort mode:
That’s an idea you might find familiar if you read my previous post on Row Goals and Grouping. In that entry, we saw how a Sort followed by a Stream Aggregate can sometimes be collapsed into a Sort iterator running in Sort Distinct mode.
The General Sorting Algorithm
SQL Server’s normal sorting algorithms are suited to a very wide range of ordering requirements. They work extremely well regardless of the data types involved, the size of data to be sorted, or the number of sort keys specified. They also make good use of available memory resources, and can spill to tempdb if required.
It is a common misconception that SQL Server will try to perform a sort entirely in memory if it can. In fact the algorithms used are much more complex: they aim to achieve a balance between memory usage, average response time, while maintaining high levels of resource concurrency. Memory is a precious resource in the server, so SQL Server may spill a sort to tempdb, even if sufficient main memory is available. Read More...