Efficient paging with large tables in sql 2008

Question

for tables with > 1,000,000 rows and possibly many many more !

haven't done any benchmarking myself so wanted to get the experts opinion.

Looked at some articles on row_number() but it seems to have performance implications

What are the other choices/alternatives ?

Answer 1

We use row_number() to great effect and there hasn't really been any performance issues with it. The basic structure of our paginated queries looks like this:

WITH result_set AS (
  SELECT
    ROW_NUMBER() OVER (ORDER BY <ordering>) AS [row_number],
    x, y, z
  FROM
    table
  WHERE
    <search-clauses>
) SELECT
  *
FROM
  result_set
WHERE
  [row_number] BETWEEN a AND b

It works fine for us on tables with > 1,000,000 rows.