簡析基於SQL SERVER分頁預存程序的演化

針對資料庫資料在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)

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

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 (運算式) 新功能，避免了字串拼串，使結構化查詢語言 (SQL)變得簡潔。實現的為同樣的功能。

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) 

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 語言對分頁功能的支援進步不少。使分頁實現趨向於簡單化。