Comparison of TOPN clause and set ROWCOUNTN

Q: In order to return a specified number of rows from a query, is it quicker to use the TOPN clause than the SET ROWCOUNTN statement?

A: The top n clause is as fast as the SET ROWCOUNT N statement when the index is properly indexed, but top n is faster if the data is not sorted. In the case of an unordered entry, the top N operation uses a sorted, small intermediate temporary table, and the operation replaces only the last row of the table. If the input is approximate, the top N engine must delete or insert the last row, but only a few operations. Approximate ordering means that the heap you are working on can be inserted in an orderly manner when the initial build, and does not require a lot of updates, deletions, moves to move the pointer forward, and so on.

Sorting an approximate sort of heap is more efficient than sorting a huge table. In one test, Top n was used to sort a table that was built with the same number of rows by an unordered insert operation, and found that the high n was not efficient. Typically, I/O times are the same when indexes are made and not indexed, but SQL Server must perform a full table scan without indexing. Processor time and elapsed time indicate that an approximate sort of heap is more efficient. The I/O time is the same, however, because SQL Server reads all the rows anyway.