How to Implement Custom Pagination in SQL Server Using OFFSET/FETCH and ROW_NUMBER()?

SQL Server custom paging: OFFSET/FETCH and ROW_NUMBER() methods

SQL pagination is used to retrieve a limited number of records from a dataset and is typically used to display the results in pages on a user interface. The goal of this article is to implement paging functionality for a query that retrieves posts and their latest entries.

SQL Server 2012 and later versions can use the OFFSET and FETCH NEXT keywords to implement paging. The following query demonstrates how to use these keywords for pagination:

<code class="language-sql">SELECT PostId
FROM (
    SELECT PostId, MAX(Datemade) AS LastDate
    FROM dbForumEntry
    GROUP BY PostId
) AS SubQueryAlias
ORDER BY LastDate DESC
OFFSET 10 ROWS    -- 跳过10行
FETCH NEXT 10 ROWS ONLY;  -- 获取接下来的10行</code>

For versions prior to SQL Server 2012, a different approach is required. One way is to use the ROW_NUMBER() function to assign a row number to a record and then use the WHERE clause to filter the desired rows:

<code class="language-sql">DECLARE @Start INT, @End INT;
SELECT @Start = 10, @End = 20;

WITH PostCTE AS (
    SELECT PostId, MAX(Datemade) AS LastDate,
    ROW_NUMBER() OVER (ORDER BY PostId) AS RowNumber
    FROM dbForumEntry
    GROUP BY PostId
)
SELECT PostId, LastDate
FROM PostCTE
WHERE RowNumber BETWEEN @Start AND @End; -- 获取第10到20行</code>

This approach provides a way to implement paging for earlier versions of SQL Server, allowing you to control the number of records displayed and facilitate navigation of large data sets.

The above is the detailed content of How to Implement Custom Pagination in SQL Server Using OFFSET/FETCH and ROW_NUMBER()?. For more information, please follow other related articles on the PHP Chinese website!