解决方法
对于一个大小的表,使用Common-Table Expression(CTE)和ROW_NUMBER;使用一个小的函数来计算基于@PageNumber和@PageSize变量(或任何你想要调用它们)的记录.我们的一个存储过程的简单例子：
-- 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
