分頁方案一：(利用Not In和SELECT TOP分頁)
例子：
每頁顯示10條記錄，顯示第一頁記錄：
SELECT TOP 10 * FROM Production.Product WHERE
(ProductID not in (SELECT TOP 0 ProductID FROM Production.Product ORDER BY ProductID))
ORDER BY ProductID
結果如下：
每頁顯示10條記錄，顯示第二頁記錄：
SELECT TOP 10 * FROM Production.Product WHERE
(ProductID not in (SELECT TOP 10 ProductID FROM Production.Product ORDER BY ProductID))
ORDER BY ProductID
結果如下：
可以總結為：
SELECT TOP 頁大小 * FROM Table WHERE
(ID NOT IN (SELECT TOP 頁大小*(頁數-1) ID FROM Table ORDER BY ID))
ORDER BY ID
分頁方案二：(利用SQL Server 2005新增的 ROW_NUMBER()分頁)
例子：
每頁顯示10條記錄，顯示第一頁記錄：
WITH Products AS
(SELECT
ROW_NUMBER() OVER (order by ProductID) as RowNumber,*
FROM Production.Product )
SELECT *
FROM Products
WHERE RowNumber between 1 and 10;
結果如下：
每頁顯示10條記錄，顯示第二頁記錄：
WITH Products AS
(SELECT
ROW_NUMBER() OVER (order by ProductID) as RowNumber,*
FROM Production.Product )
SELECT *
FROM Products
WHERE RowNumber between 11 and 20;
結果如下：
可以總結為：
WITH Products AS
(SELECT
ROW_NUMBER() OVER (order by ProductID) as RowNumber,*
FROM Production.Product )
SELECT *
FROM Products
WHERE RowNumber between 頁大小*(頁數-1)+1 and 頁大小*頁數;
