Which paging method (Sql Server 2008) for BEST performance?

Question

In Sql Server 2008, many options are available for database paging via stored procedure. For example, see here and here.

OPTIONS:

1. ROW_NUMBER() function
2. ROWCOUNT
3. CURSORS
4. temporary tables
5. Nested SQL queries
6. OTHERS

Paging using ROW_NUMBER() is known to have performance issues:

Please advise, which paging method has the best performance (for large tables with JOINs) ?

Please also provide links to relevant article(s), if possible.

Thank You.

Answer 1

One question you have to answer is if you want to display the total number of rows to the end user. To calculate the number of the last page, you also need the last row number.

If you can do without that information, a temporary table is a good option. You can select the pirmary key and use LIMIT to retrieve keys up to the key you're interested in. If you do this right, the typical use case will only retrieve the first few pages.

If you need the last page number, you can use ROW_NUMBER(). Using a temporary table won't be much faster because you can't use the LIMIT clause, making this strategy the equivalent of a ROW_NUMBER() calculation.