How to do pagination in SQL Server 2008

Question

How do you do pagination in SQL Server 2008 ?

Answer 1

You can use ROW_NUMBER():

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Example:

WITH CTEResults AS (     SELECT IDColumn, SomeField, DateField, ROW_NUMBER() OVER (ORDER BY DateField) AS RowNum     FROM MyTable )  SELECT *  FROM CTEResults WHERE RowNum BETWEEN 10 AND 20;