分頁是應用系統最常見需求。SQL Server 歷經多版本演進,從 NOT IN 子查詢到 OFFSET/FETCH,效能與可讀性大幅提升。
SELECT TOP @PageSize *
FROM Articles
WHERE Id NOT IN (
SELECT TOP @Offset Id FROM Articles ORDER BY Id DESC
)
ORDER BY Id DESC;
@Offset = (@PageIndex - 1) * @PageSizeSELECT TOP @PageSize *
FROM Articles
WHERE Id < (
SELECT MIN(Id) FROM (
SELECT TOP @Offset Id FROM Articles ORDER BY Id DESC
) AS Tmp
)
ORDER BY Id DESC;
CREATE PROCEDURE GetPagedData
@PageSize INT, @PageIndex INT, @TotalPages INT OUTPUT
AS
BEGIN
DECLARE @Total INT, @Start INT, @End INT;
DECLARE cur SCROLL CURSOR FOR SELECT * FROM Articles ORDER BY Id;
OPEN cur;
SET @Total = @@CURSOR_ROWS;
IF @Total > 0
BEGIN
SET @TotalPages = CASE WHEN @Total % @PageSize = 0
THEN @Total / @PageSize
ELSE @Total / @PageSize + 1 END;
SET @Start = (@PageIndex - 1) * @PageSize + 1;
FETCH ABSOLUTE @Start FROM cur;
-- 逐列 FETCH @PageSize 次...
END
CLOSE cur; DEALLOCATE cur;
END;
WITH Paged AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY Id DESC) AS RowNum
FROM Articles
)
SELECT * FROM Paged
WHERE RowNum BETWEEN @Offset + 1 AND @Offset + @PageSize;
SELECT * FROM Articles
ORDER BY Id DESC
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
-- 第一頁
SELECT TOP 20 * FROM Articles ORDER BY Id DESC;
-- 後續頁 (記住上一頁最後一筆 Id = @LastId)
SELECT TOP 20 * FROM Articles
WHERE Id < @LastId
ORDER BY Id DESC;
| 寫法 | 邏輯讀取 | CPU (ms) | 耗時 (ms) | 適用場景 |
|---|---|---|---|---|
| NOT IN | 極高 | >10000 | >10000 | 極小表/教學 |
| MAX/MIN | 高 | ~500 | ~800 | 舊版本/有序鍵 |
| 游標 | 中 | ~300 | ~500 | 低併發/程式控制 |
| ROW_NUMBER | 中高 | ~200 | ~300 | 通用標準 |
| OFFSET/FETCH | 中高 | ~200 | ~300 | SQL 2012+ 標準 |
| Keyset | 極低 | ~10 | ~20 | 巨量資料/無限捲動 |
COUNT(*) 或視窗函數 COUNT(*) OVER()分頁無銀彈,理解資料分佈、存取模式、業務需求,選對策略事半功倍。