TOP vs SET ROWCOUNT | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive


Interested in comments of others on performance of performance of ordered and unordered TOP statement.
Assume I have three queries
(a) SELECT TOP X * from some_table order by SOMECOLUMN DESC
(b) SELECT TOP X * from some_table
SELECT * from some_table
In all three cases, sql needs to do a complication (parsing, checking for valid table and column names) and optimization before returning the results. however, would it be resonable to expect queries 2 and 3 executing faster on a large some_table size?
What will engine do in case (a) – would it actually need to get the whole recordset, order it and give me top X out of it?
According to Kalen Delaney, statements (b) and (c) are equivalent , with (b) having slightly better performance. Thank you, comments welcome.

Upps, sorry – posted to the wrong forum (meant to go to Peformance Tuning for Developers). Moderators, please move if you can..
Done. BTW: You can do that with your posts.
Luis Martin
Moderator All postings are provided “AS IS” with no warranties for accuracy.
With proper indexes, the TOP N clause and SET ROWCOUNT N statement are equally fast, but with unsorted input from a heap, TOP N is faster. Usually, the I/O time is the same both with an index and without; however, without an index SQL Server must do a complete table scan. Processor time and elapsed time show the efficiency of the nearly sorted heap. The I/O time is the same because SQL Server must read all the rows either way.
Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.