學海無邊，書囊無底

當查詢的筆數過多時，不建議將所有的資料往用戶端送。因為傳送過多的資料，會讓有限的網路頻寬影響傳送的效能。此外，SQL Server需等待使用者端程式處理完一批資料後，才能傳送下一批，導致前端程式效能不佳，也拖累SQL Server。且取得過多的資料也會讓人無法分析，大量細節資料下傳意義不大。

基於上述原因，會視需要而將結果集分頁。舉例來說，假設符合條件的資料有1000筆，而網頁上每頁所呈現的資料為25筆資料。只要查詢當下各頁時，由資料庫中取得符合的25筆資料傳至用戶端即可。不需將1000 筆資料整批傳送至前端，再由前端程式挑出25筆記錄呈現。

以下使用不同版本的SQL Server撰寫預存程序提供的分頁功能示範如何將取得的資料集給與適當的編號，並透過參數指定每頁要呈現的資料筆數 。

適用版本：SQL 2012

透過SQL Server 2012提供的新功能(OFFSET與FETCH)達到分頁效果。
OFFSET：決定從第幾行開始顯示資料
FETCH NEXT：針對資料指標的第一項提取，它會傳回結果集中的第一個資料列。用於決定你要列出幾筆 。

 PROC dbo.usp_GetSomeData2
  @colname AS sysname, 
  @sortdir AS NCHAR(4) = 'DESC',
  @PageIndex int,			--取得第@PageIndex 頁資料
  @PageRow int		     --一頁 @PageRow　筆資料
AS
DECLARE @msg AS NVARCHAR(500);
PRINT @sortdir
IF @sortdir NOT IN ('ASC',N'DESC')
BEGIN
  SET @msg = N'請輸入ASC 或DESC';
  RAISERROR(@msg, 16, 1);
  RETURN;
END

DECLARE @StartRow  int,　--分頁內的第一筆
		@EndRow    int 　--分頁內的最後一筆
			
	set @EndRow = @PageIndex * @PageRow
	set @StartRow = (@EndRow - @PageRow) 
SELECT CustomerID,CompanyName, PostalCode,ContactName, ContactTitle,Phone
FROM dbo.Customers
ORDER BY
	  CASE WHEN @colname = N'CustomerID'   AND @sortdir = 'ASC'
		THEN CustomerID   END,
	  CASE WHEN @colname = N'CompanyName' AND @sortdir = 'ASC'
		THEN CompanyName END, 
	  CASE WHEN @colname = N'CustomerID'   AND @sortdir = 'DESC'
		THEN CustomerID   END DESC,
	  CASE WHEN @colname = N'CompanyName' AND @sortdir = 'DESC'
		THEN CompanyName END DESC 
OFFSET @StartRow ROWS         --決定從第幾行開始顯示資料
FETCH NEXT @PageRow ROWS ONLY --決定你要秀出幾筆
 

使用下列語法呼叫預存程序

EXEC dbo.usp_GetSomeData2 'CompanyName' ,'ASC',2,10

 

適用版本：SQL 2005'、SQL 2008、SQL 2008R2

 

CREATE PROC dbo.usp_GetSomeData
  @colname AS sysname, 
  @sortdir AS NCHAR(4) = 'DESC',
  @PageIndex int,			--取得第@PageIndex 頁資料
  @PageRow int		     --一頁 @PageRow　筆資料
AS
DECLARE @msg AS NVARCHAR(500);
PRINT @sortdir
IF @sortdir NOT IN ('ASC',N'DESC')
BEGIN
  SET @msg = N'請輸入ASC 或DESC';
  RAISERROR(@msg, 16, 1);
  RETURN;
END

DECLARE @StartRow  int,　--分頁內的第一筆
		@EndRow    int 　--分頁內的最後一筆
	
	set @EndRow = @PageIndex * @PageRow
	set @StartRow = (@EndRow - @PageRow) + 1
SELECT * FROM (
SELECT ROW_NUMBER() over(order by 
CASE WHEN @colname = N'CustomerID'   AND @sortdir = 'ASC'
    THEN CustomerID   END,
  CASE WHEN @colname = N'CompanyName' AND @sortdir = 'ASC'
    THEN CompanyName END,
   CASE WHEN @colname = N'CustomerID'   AND @sortdir = 'DESC'
    THEN CustomerID   END DESC,
  CASE WHEN @colname = N'CompanyName' AND @sortdir = 'DESC'
    THEN CompanyName END DESC
 ) AS nub
,CustomerID,CompanyName, PostalCode,ContactName, ContactTitle,Phone
FROM dbo.Customers
) tblA WHERE nub between @StartRow and @EndRow

上述程式碼使用了 Row_Number() 函數，賦予每筆資料唯一的序號。接著利用參數@StartRow與@EndRow，計算當指定每頁 @PageRow 筆資料時，第 @PageIndex 頁的第一筆資料序號到最後一筆資料序號的編號數字。最後，在WHERE子句中，指定要回傳的序號範圍。

接著使用下列程式碼執行預存程序 usp_GetSomeData ，輸入查詢結內容依 CompanyName 欄位由小至大排序，每頁10筆資料，回傳第二頁資料

EXEC dbo.usp_GetSomeData 'CompanyName' ,'ASC',2,15

 

執行結果如下圖所示，依上述條件所回傳的資料只會取第16筆至第30筆資料。

 

參考：線上說明、學無止盡