資料庫分頁；簡單整理測試

針對資料庫資料在UI介面上的分頁是老生常談的問題了，網上很容易找到各種“通用儲存過程”程式碼，而且有些還定製查詢條件，看上去使用很方便。筆者打算通過本文也來簡單談一下基於SQL SERVER 2000的分頁儲存過程，同時談談SQL SERVER 2005下分頁儲存過程的演進。 


在進行基於UI顯示的資料分頁時，常見的資料提取方式主要有兩種。第一種是從資料庫提取所有資料然後在系統應用程式層進行資料分頁，顯示當前頁資料。第二種分頁方式為從資料庫取出需要顯示的一頁資料顯示在UI介面上。

以下是筆者對兩種實現方式所做的優缺點比較，針對應用程式編寫，筆者以.NET技術平臺為例。

 類別   SQL語句                程式碼編寫        設計時          效能
 
第一種  語句簡單，相容性好        很少         完全支援     資料越大效能越差
 
第二種  看具體情況                較多        部分支援     良好，跟SQL語句有關
 

 
對於第一種情況本文不打算舉例，第二種實現方式筆者只以兩次TOP方式來進行討論。
在編寫具體SQL語句之前，定義以下資料表。
資料表名稱為：Production.Product。Production為SQL SERVER 2005中改進後的資料表架構，對舉例不造成影響。
包含的欄位為：

列名           資料型別           允許空          說明
 
ProductID          Int                                         產品ID，PK。
 
Name          Nvarchar(50)                                  產品名稱。
不難發現以上表結構來自SQL SERVER 2005 樣例資料庫AdventureWorks的Production.Product表，並且只取其中兩個欄位。 
分頁相關元素：
PageIndex – 頁面索引計數，計數0為第一頁。
PageSize – 每個頁面顯示大小。
RecordCount – 總記錄數。
PageCount – 頁數。 


對於後兩個引數，筆者在儲存過程中以輸出引數提供。
 

1．SQL SERVER 2000中的TOP分頁
Create PROCEDURE [Zhzuo_GetItemsPage]
    @PageIndex INT, /*@PageIndex從計數,0為第一頁*/
    @PageSize  INT, /*頁面大小*/
    @RecordCount INT OUT, /*總記錄數*/
    @PageCount INT OUT /*頁數*/

AS

/*獲取記錄數*/
Select @RecordCount = COUNT(*) FROM Production.Product
/*計算頁面資料*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/*TOP記錄數*/
DECLARE @TOPCOUNT INT
SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex 


DECLARE @SQLSTR NVARCHAR(1000) 


IF @PageIndex = 0 or @PageCount <= 1
BEGIN
    SET @SQLSTR =N'Select TOP '+STR(@PageSize)+
    'ProductID,Name FROM Production.Product orDER BY ProductID DESC'
END
ELSE
BEGIN 
    IF @PageIndex = @PageCount - 1
    BEGIN
       SET @SQLSTR =N'Select * FROM ( Select TOP ' + STR(@TOPCOUNT) +
       'ProductID,Name FROM Production.Product orDER BY ProductID ASC) T orDER BY ProductID DESC'
    END
    ELSE
    BEGIN
       SET @SQLSTR =N' Select TOP '+STR(@PageSize)+'* FROM (Select TOP ' + STR(@TOPCOUNT) +
       'ProductID,Name FROM Production.Product orDER BY ProductID ASC) T orDER BY ProductID DESC'
    END
END
/*執行*/
EXEC (@SQLSTR)



以上儲存過程對頁數進行判斷，如果是第一頁或最後一頁，進行特殊處理。其他情況使用2次TOP翻轉。其中排序條件為ProductID倒序。最後通過EXECUTE執行SQL字串拼串。 


2．SQL SERVER 2005中的TOP分頁
Create PROCEDURE [dbo].[Zhzuo_GetItemsPage2005TOP]
    @PageIndex INT,
    @PageSize  INT,
    @RecordCount INT OUT,
    @PageCount INT OUT
AS 

/*獲取記錄數*/
Select @RecordCount = COUNT(*) FROM Production.Product
/*計算頁面資料*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/*TOP記錄數*/
DECLARE @TOPCOUNT INT
SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex 


/*基於SQL SERVER 2005 */
IF @PageIndex = 0 or @PageCount <= 1
BEGIN
    Select TOP(@PageSize) ProductID,Name FROM Production.Product orDER BY ProductID DESC
END
ELSE
BEGIN 
    IF @PageIndex = @PageCount - 1
    BEGIN
       Select * FROM ( Select TOP(@TOPCOUNT) ProductID,Name FROM Production.Product orDER BY ProductID ASC) T
       orDER BY ProductID DESC
    END
    ELSE
    BEGIN
       Select TOP(@PageSize) * FROM (Select TOP(@TOPCOUNT) ProductID,Name FROM Production.Product orDER BY ProductID ASC) T
       orDER BY ProductID DESC
    END
END 
以上儲存過程是使用2005的TOP (表示式) 新功能，避免了字串拼串，使結構化查詢語言變得簡潔。實現的為同樣的功能。 


3．SQL SERVER 2005中的新分頁
Create PROCEDURE [dbo].[Zhzuo_GetItemsPage2005]
    @PageIndex INT,
    @PageSize  INT,
    @RecordCount INT OUT,
    @PageCount INT OUT
AS 

/*獲取記錄數*/
Select @RecordCount = COUNT(*) FROM Production.Product
/*計算頁面資料*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/* 基於SQL SERVER 2005 */
Select SerialNumber,ProductID,Name FROM
(Select ProductID,Name,ROW_NUMBER() OVER (ORDER BY ProductID DESC) AS SerialNumber FROM Production.Product ) AS T
Where T.SerialNumber > (@PageIndex * @PageSize)  and T.SerialNumber <= ((@PageIndex+1) * @PageSize)
 

第三個儲存過程使用2005下新的功能，實現的分頁儲存過程功能更加簡單明瞭，而且更加容易理解。注意這裡的ProductID為主鍵,根據ProductID進行排序生成ROW_NUMBER，通過ROW_NUMBER來確定具體的頁數。 


通過對三個分頁儲存過程的比較，可見SQL SERVER 的TSQL 語言對分頁功能的支援進步不少。使分頁實現趨向於簡單化。