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.

Answer 2

Hi guys!

kodeka, i have a question. Are you experiencing some differences in query execution performance when fetching first rows and last rows? In my deployment fetching last rows takes 1000 times more than fetching first ones.

Thank you!