SQL Server Performance Forum – Threads Archive

TOP vs SET ROWCOUNT

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
(c) SET ROWCOUNT X
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.
Simas

Upps, sorry – posted to the wrong forum (meant to go to Peformance Tuning for Developers). Moderators, please move if you can..
simas

Done. BTW: You can do that with your posts.
Luis Martin
Moderator
SQL-Server-Performance.com 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
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>