| 導購 | 订阅 | 在线投稿
分享
 
 
 

簡單談基于SQL SERVER 分頁存儲過程的演進

來源:互聯網網民  2006-12-10 06:53:51  評論

簡單談基于SQL SERVER 分頁存儲過程的演進

作者:鄭佐

日期:2006-9-30

針對數據庫數據在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 語言對分頁功能的支持進步不少。使分頁實現趨向于簡單化。

 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
簡單談基于SQL SERVER 分頁存儲過程的演進 作者:鄭佐 日期:2006-9-30 針對數據庫數據在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 語言對分頁功能的支持進步不少。使分頁實現趨向于簡單化。
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 
 熱帖排行
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有