本文將介紹如何透過 SQL Server 2012 強化的 ORDER BY 敘述來做資料分頁。
從 SQL Server 2005 開始，提供了 ROW_NUMBER 函數，用來回傳分割內的資料列序號，每個分割的序號都是由 1 開始，通常我們會使用由 ROW_NUMBER 函數產生的序號來當作呈現在前端的流水號，或是做到如下列 T-SQL 敘述的資料分頁，讓擅長處理大量資料的 SQL Server 可以回傳前端 AP 所需要的內容就好，而不需要整個查詢結果都傳到前端後才由 AP 去做分頁，藉此提升查詢的效能。
1: use Northwind
2: go
3: declare @PageIndex int = 2
4: declare @PageSize int = 10
5:
6: --使用 Row_Number 產生行號再進行分頁
7: select *
8: from
9: (
10: select ROW_NUMBER() over (order by CustomerID) as sn,*
11: from Customers
12: )t
13: where sn between (@PageIndex * @PageSize) + 1 and (@PageIndex + 1) * @PageSize
【執行結果】
到了 SQL Server 2012 擴充了 Order By 子句來提供更直覺的 Ad-hoc 查詢分頁，您可以透過【offset … rows】敘述來告訴 SQL Server 要忽略多少列的資料，才開始回傳資料，並搭配【fetch next … rows】來告訴 SQL Server 每次查詢要回傳多少筆資料。假設您忽略【fetch next … rows】敘述，則會從【offset … rows】忽略的資料列之後開始回傳剩下的資料。
下列程式碼用來示範如何利用 Order By 子句新的【offset … rows】和 【fetch next … rows】敘述來做資料分頁。
1: use Northwind
2: go
3: declare @PageIndex int = 2
4: declare @PageSize int = 10
5:
6: --使用Offset 和 Fetch 進行資料分頁
7: select *
8: from Customers
9: order by CustomerID
10: offset @PageIndex * @PageSize row
11: fetch next @PageSize rows only
【執行結果】
【參考資料】