Microsoft SQL Server Pagination Pagination using ROW_NUMBER with a Common Table Expression
Example
SQL Server 2008
The ROW_NUMBER function can assign an incrementing number to each row in a result set. Combined with a Common Table Expression that uses a BETWEEN operator, it is possible to create 'pages' of result sets. For example: page one containing results 1-10, page two containing results 11-20, page three containing results 21-30, and so on.
WITH data
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY name) AS row_id,
object_id,
name,
type,
create_date
FROM sys.objects
)
SELECT *
FROM data
WHERE row_id BETWEEN 41 AND 50
Note: It is not possible to use ROW_NUMBER in a WHERE clause like:
SELECT object_id,
name,
type,
create_date
FROM sys.objects
WHERE ROW_NUMBER() OVER (ORDER BY name) BETWEEN 41 AND 50
Although this would be more convenient, SQL server will return the following error in this case:
Msg 4108, Level 15, State 1, Line 6
Windowed functions can only appear in the SELECT or ORDER BY clauses.
