Pagination
Pagination using ROW_NUMBER with a Common Table Expression
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.
Pagination with OFFSET FETCH
The OFFSET FETCH clause implements pagination in a more concise manner. With it, it's possible to skip N1 rows (specified in OFFSET) and return the next N2 rows (specified in FETCH):
SELECT *
FROM sys.objects
ORDER BY object_id
OFFSET 40 ROWS FETCH NEXT 10 ROWS ONLY
The ORDER BY clause is required in order to provide deterministic results.
Paginaton with inner query
In earlier versions of SQL Server, developers had to use double sorting combined with the TOP keyword to return rows in a page:
SELECT TOP 10 *
FROM
(
SELECT
TOP 50 object_id,
name,
type,
create_date
FROM sys.objects
ORDER BY name ASC
) AS data
ORDER BY name DESC
The inner query will return the first 50 rows ordered by name. Then the outer query will reverse the order of these 50 rows and select the top 10 rows (these will be last 10 rows in the group before the reversal).
Paging in Various Versions of SQL Server
SQL Server 2012 / 2014
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 4
SELECT OrderId, ProductId
FROM OrderDetail
ORDER BY OrderId
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY
SQL Server 2005/2008/R2
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 4
SELECT OrderId, ProductId
FROM (
SELECT OrderId, ProductId, ROW_NUMBER() OVER (ORDER BY OrderId) AS RowNum
FROM OrderDetail) AS OD
WHERE OD.RowNum BETWEEN ((@PageNumber - 1 ) * @RowsPerPage) + 1
AND @RowsPerPage * @PageNumber
SQL Server 2000
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 4
SELECT OrderId, ProductId
FROM (SELECT TOP (@RowsPerPage) OrderId, ProductId
FROM (SELECT TOP ((@PageNumber)*@RowsPerPage) OrderId, ProductId
FROM OrderDetail
ORDER BY OrderId) AS OD
ORDER BY OrderId DESC) AS OD2
ORDER BY OrderId ASC
SQL Server 2012/2014 using ORDER BY OFFSET and FETCH NEXT
For getting the next 10 rows just run this query:
SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Key points to consider when using it:
ORDER BYis mandatory to useOFFSETandFETCHclause.OFFSETclause is mandatory withFETCH. You can never use,ORDER BY…FETCH.TOPcannot be combined withOFFSETandFETCHin the same query expression.
Syntax:
- SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Contributors
Topic Id: 6874
Example Ids: 23295,23296,23297,26338,26339
This site is not affiliated with any of the contributors.