如何於SQL Server端進行查詢結果的分頁

2011-09-30

4840
0
TSQL

本文將介紹利用T-SQL來將資料在SQL Server端就分好頁，每次只回傳特定頁數的資料至前端，以減少資料傳輸及網路頻寬的使用，進而嘗試提升應用程式之效能。

ASP.NET提供GridView、FormView等各種資料繫結控制項（以下簡稱控制項），來協助開發人員很容易的把資料來源的資料呈現在網頁上並且提供分頁功能（本文以SQL Server 2005（含以上）為資料來源），上述控制項提供的分頁功能背後是將所有資料從資料來源下載到IIS之後，只把PageIndex所指的頁面Render到前端給使用者看，當資料量很大時，來往Web Server和SQL Server之間的資料量及所占用的頻寬將跟著放大。若能在後端資料庫就先將資料分好頁，利用資料庫善於除裡大量資料的特性，相信對呈現大量資料的效能會有所助益。

本文以Northwind的Customers資料表為例，透過ROW_NUMBER來針對CustomerID欄位來排序並產生編號，最後將這個編號作為分頁的依據。下圖為Customers資料表的內容，總共有91筆資料。

以下程式碼示範透過Stored Procedure來將資料分頁：

   1:  IF EXISTS (SELECT * FROM sys.objects WHERE type='P' AND name='usp_GetCustomersByPage')
   2:      DROP PROCEDURE usp_GetCustomersByPage
   3:  GO
   4:   
   5:  CREATE PROCEDURE usp_GetCustomersByPage
   6:  (
   7:      @PageSize int = 10            --每頁筆數，每頁不得小於1筆
   8:      ,@PageIndex int = 0            --目前頁數，第一頁的PageIndex為0
   9:  )
  10:  AS
  11:  BEGIN
  12:      DECLARE @TotalPage int = ((SELECT COUNT(CustomerID)
  13:                                                    FROM Customers) / @PageSize)
  14:                                                    
  15:      IF @PageSize < 1 SET @PageSize = 1            --若每頁筆數小於1筆，則設定為每頁1筆
  16:      IF @PageIndex < 0 SET @PageIndex = 0        --若嘗試取得的頁數小於第1頁，則重設為第1頁
  17:      IF @PageIndex > @TotalPage SET @PageIndex = @TotalPage --若嘗試取得的頁數超過總頁數，則回傳最後一頁
  18:      
  19:      ;WITH cte_customer
  20:      AS
  21:      (
  22:      --TODO：為示範方便本文使用SELECT *，建議實務上盡量避免
  23:      SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS SN,*
  24:      FROM Customers
  25:      )
  26:      
  27:      SELECT * 
  28:      FROM cte_customer
  29:      WHERE SN BETWEEN (@PageSize * @PageIndex +1 )  AND (@PageSize * (@PageIndex + 1))
  30:  END
  31:  GO