使用 SQL Server 2005 的最佳分页解决方案?

2022-01-01 00:00:00 sql sql-server-2005 tsql sql-server pagination

对大约 5,000-10,000 行的表使用 SQL Server 2005 的最有效分页解决方案是什么?我见过几个，但没有比较它们.

What is the most efficient paging solution using SQL Server 2005 against a table with around 5,000-10,000 rows? I've seen several out there but nothing comparing them.

推荐答案

对于这么大的表，使用公共表表达式 (CTE) 和 ROW_NUMBER；使用一个小函数来计算基于 @PageNumber 和 @PageSize 变量(或您想调用它们的任何内容)带回来的记录.我们存储过程之一的简单示例:

For a table that size, use a Common-Table Expression (CTE) and ROW_NUMBER; use a small function to calculate the records to bring back based on @PageNumber and @PageSize variables (or whatever you want to call them). Simple example from one of our stored procedures:

-- calculate the record numbers that we need

DECLARE @FirstRow INT, @LastRow INT
SELECT  @FirstRow   = ((@PageNumber - 1) * @PageSize) + 1,
        @LastRow    = ((@PageNumber - 1) * @PageSize) + @PageSize

;
WITH CTE AS
(
    SELECT [Fields]
           , ROW_NUMBER() OVER (ORDER BY [Field] [ASC|DESC]) as RowNumber 
    FROM [Tables]
    WHERE [Conditions, etc]
)
SELECT * 
       -- get the total records so the web layer can work out
       -- how many pages there are
       , (SELECT COUNT(*) FROM CTE) AS TotalRecords
FROM CTE
WHERE RowNumber BETWEEN @FirstRow AND @LastRow
ORDER BY RowNumber ASC

相关文章