我最喜欢的SQL分页查询方法

USE AdventureWorks2012;
GO

DECLARE @PageSize int = 100, @PageNumber int = 3;

WITH Product_CTE
AS
(
    SELECT Name
          ,ProductNumber
          ,StandardCost
          ,ListPrice
          ,SellStartDate
          ,ROW_NUMBER() OVER(ORDER BY ProductID) AS RowNumber
          ,COUNT(*) OVER() AS TotalCount
    FROM Production.Product
)
SELECT * 
FROM Product_CTE
WHERE RowNumber BETWEEN @PageSize * (@PageNumber - 1) + 1 AND @PageSize * @PageNumber;