Amol Naik-681410 (5/12/2010)
I always had this question in mind, isn't there a performance penalty in using this approach on the DB side. Imaging if there are several thousand records, SQL Server still has to order those records using ROW_NUMBER(). Wouldn't the performance suffer at some point of time? What is your opinion? Right now there is a debate within our DBA team to implement this or not because of performance issues.
You will probably have noticed that SQL Server does not have to do any ordering - there are no Sort operators in the query plans shown - the index provides the order.
The Key Seek method, properly applied, is one of the fastest ways I know of to page a result set. I have used this method on large sets of data on massively concurrent systems. Properly configured, it is extremely hard to beat.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi