分頁是 Web 應用核心需求。SQL Server 2000 無窗口函數，需臨時表+身份列模擬；2005 引入 ROW_NUMBER() 實現原生高效分頁。
CREATE PROCEDURE dbo.P_GetPagedOrders2000
@StartIndex INT, @PageSize INT
AS
SET NOCOUNT ON
DECLARE @IndexTable TABLE (ID INT IDENTITY(1,1), NID INT)
DECLARE @UpperBound INT = @StartIndex + @PageSize - 1
SET ROWCOUNT @UpperBound
INSERT INTO @IndexTable (NID) SELECT OrderID FROM Orders ORDER BY OrderID DESC
SELECT O.OrderID, O.OrderDate, O.CustomerID, C.CompanyName, E.FirstName+' '+E.LastName AS EmployeeName
FROM Orders O
LEFT JOIN Customers C ON O.CustomerID = C.CustomerID
LEFT JOIN Employees E ON O.EmployeeID = E.EmployeeID
INNER JOIN @IndexTable T ON O.OrderID = T.NID
WHERE T.ID BETWEEN @StartIndex AND @UpperBound
ORDER BY T.ID
SET NOCOUNT OFF缺點：SET ROWCOUNT 影響後續語句、臨時表開銷大、無法利用索引覆蓋。
CREATE PROCEDURE dbo.P_GetPagedOrders2005
@StartIndex INT, @PageSize INT
AS
WITH OrderList AS (
SELECT ROW_NUMBER() OVER (ORDER BY O.OrderID DESC) AS RowNum,
O.OrderID, O.OrderDate, O.CustomerID, C.CompanyName, E.FirstName+' '+E.LastName AS EmployeeName
FROM Orders O
LEFT JOIN Customers C ON O.CustomerID = C.CustomerID
LEFT JOIN Employees E ON O.EmployeeID = E.EmployeeID
)
SELECT OrderID, OrderDate, CustomerID, CompanyName, EmployeeName
FROM OrderList
WHERE RowNum BETWEEN @StartIndex AND @StartIndex + @PageSize - 1| 維度 | 2000 臨時表法 | 2005 ROW_NUMBER() |
|---|---|---|
| 可讀性 | 低（多步驟、副作用） | 高（單語句、聲明式） |
| 性能 | 需物化中間結果 | 優化器可推導谓詞下推、索引覆蓋 |
| 並發 | SET ROWCOUNT 會話級副作用 | 無副作用 |
| 總行數 | 需額外 COUNT(*) | COUNT(*) OVER() 同查詢獲取 |
SELECT * FROM Orders
ORDER BY OrderID DESC
OFFSET @StartIndex ROWS FETCH NEXT @PageSize ROWS ONLY語法更簡潔，執行計劃與 ROW_NUMBER() 基本一致。
