How Can I Efficiently Paginate Results in SQL Server, Considering Performance and Total Count Retrieval?

Efficient Paging: Performance and Total Retrieval in SQL Server

In the world of database management, efficient paging is crucial to handle large data sets and provide a user-friendly browsing experience. This article explores best practices for result paging in Microsoft SQL Server 2000 to 2012 versions, taking into account performance and total number of retrieved results.

SQL Server 2000-2008: Row Numbers and COUNT

For earlier versions of SQL Server, a common approach was to use the ROW_NUMBER() function to assign a serial number to each row in the table. This serial number can then be used to filter the results based on the desired page number and page size. To retrieve the total number, you can use the COUNT() function as a subquery.

Example:

-- 分页
SELECT *
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNum, *
    FROM TableName
) AS PaginatedTable
WHERE RowNum BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)

-- 总数
SELECT COUNT(*) AS TotalCount
FROM TableName

SQL Server 2012: OFFSET and FETCH

With the introduction of SQL Server 2012, the OFFSET and FETCH clauses provide a more direct and efficient method of paging.

Example:

SELECT *
FROM TableName
ORDER BY id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

This query skips the first 10 rows (OFFSET 10 ROWS) and retrieves the next 10 rows (FETCH NEXT 10 ROWS ONLY). The ORDER BY clause is necessary for OFFSET and FETCH to work correctly.

Note:

• ORDER BY is required when using OFFSET and FETCH.
• OFFSET must be used with FETCH.
• In the same query expression, TOP cannot be used in combination with OFFSET and FETCH.

Conclusion

The best way to paginate results in SQL Server depends on the specific version used. While the ROW_NUMBER() method is still possible with older versions, OFFSET and FETCH provide a more efficient and simplified solution in SQL Server 2012 and later versions.

The above is the detailed content of How Can I Efficiently Paginate Results in SQL Server, Considering Performance and Total Count Retrieval?. For more information, please follow other related articles on the PHP Chinese website!