How to Implement Paging in SQL Server to Retrieve Specific Row Ranges?

Detailed explanation of SQL Server paging technology

Paging (or skipping a specific number of rows and getting a specified number of rows) is a common feature in database systems. The custom paging function can flexibly control the data retrieval process. This article demonstrates how to implement paging in different versions of SQL Server, including SQL Server 2012 and earlier.

Inquiry requirements

Suppose there is a query to retrieve post IDs from a database table. The current query retrieves the top 10 post IDs based on the latest date. The goal is to implement pagination so that a specific number of rows can be skipped and fetched, thus retrieving the "10th to 20th most recently active posts" rather than just the "top 10".

SQL Server 2012 implementation

In SQL Server 2012, pagination can be easily achieved using the OFFSET and FETCH NEXT keywords:

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

This query will skip the first 10 rows and get the next 10 rows, effectively retrieving the post IDs of the "10th to 20th" most recently active posts.

SQL Server 2008 implementation

In SQL Server 2008 and earlier versions, implementing paging is more complex. You can use a combination of the ROW_NUMBER() function and a CTE (Common Table Expression) to achieve the desired result:

<code class="language-sql">DECLARE @Start INT
DECLARE @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 > @Start AND RowNumber <= @End;</code>

In this example, two variables @Start and @End are declared to define the pagination parameters. The query first calculates the line number of each post using the ROW_NUMBER() function. It then uses CTE to filter the results based on the specified pagination parameters and retrieve the required post IDs and their latest dates.

By using appropriate techniques, custom paging can be implemented in SQL to enhance data retrieval flexibility and optimize database queries.

The above is the detailed content of How to Implement Paging in SQL Server to Retrieve Specific Row Ranges?. For more information, please follow other related articles on the PHP Chinese website!