此文章主要向大家講述的是正確實(shí)現(xiàn)小數(shù)據(jù)量與你海量數(shù)據(jù)之通用分頁SQL Server存儲(chǔ)過程的顯示，在實(shí)際操作中建立一個(gè) Web 應(yīng)用，分頁瀏覽的實(shí)際操作功能是必不可少。這個(gè)問題是數(shù)據(jù)庫處理中十分常見的問題。

經(jīng)典的數(shù)據(jù)分頁方法是:ADO 紀(jì)錄集分頁法，也就是利用ADO自帶的分頁功能（利用游標(biāo)）來實(shí)現(xiàn)分頁。但這種分頁方法僅適用于較小數(shù)據(jù)量的情形，因?yàn)橛螛?biāo)本身有缺點(diǎn)：游標(biāo)是存放在內(nèi)存中，很費(fèi)內(nèi)存。

游標(biāo)一建立，就將相關(guān)的記錄鎖住，直到取消游標(biāo)。游標(biāo)提供了對(duì)特定集合中逐行掃描的手段，一般使用游標(biāo)來逐行遍歷數(shù)據(jù)，根據(jù)取出數(shù)據(jù)條件的不同進(jìn)行不同的操作。而對(duì)于多表和大表中定義的游標(biāo)（大的數(shù)據(jù)集合）循環(huán)很容易使程序進(jìn)入一個(gè)漫長的等待甚至死機(jī)。

更重要的是，對(duì)于非常大的數(shù)據(jù)模型而言，分頁檢索時(shí)，如果按照傳統(tǒng)的每次都加載整個(gè)數(shù)據(jù)源的方法是非常浪費(fèi)資源的?，F(xiàn)在流行的分頁方法一般是檢索頁面大小的塊區(qū)的數(shù)據(jù)，而非檢索所有的數(shù)據(jù)，然后單步執(zhí)行當(dāng)前行。

最早較好地實(shí)現(xiàn)這種根據(jù)頁面大小和頁碼來提取數(shù)據(jù)的方法大概就是“俄羅斯SQL Server存儲(chǔ)過程”。這個(gè)存儲(chǔ)過程用了游標(biāo)，由于游標(biāo)的局限性，所以這個(gè)方法并沒有得到大家的普遍認(rèn)可。

后來，網(wǎng)上有人改造了此存儲(chǔ)過程，下面的存儲(chǔ)過程就是結(jié)合我們的辦公自動(dòng)化實(shí)例寫的分頁SQL Server存儲(chǔ)過程：

CREATE procedure pagination1 (@pagesize int, 頁面大小，如每頁存儲(chǔ)20條記錄 @pageindex int 當(dāng)前頁碼 ) as set nocount cellSpacing=0 cellPadding=0 width="70%" align=center border=1> 頁碼 方案1 方案2 方案3 1 60 30 76 10 46 16 63 100 1076 720 130 500 540 12943 83 1000 17110 470 250 10000 24796 4500 140 100000 38326 42283 1553 250000 28140 128720 2330 500000 121686 127846 7168

從上表中，我們可以看出，三種存儲(chǔ)過程在執(zhí)行100頁以下的分頁命令時(shí)，都是可以信任的，速度都很好。但***種方案在執(zhí)行分頁1000頁以上后，速度就降了下來。第二種方案大約是在執(zhí)行分頁1萬頁以上后速度開始降了下來。而第三種方案卻始終沒有大的降勢，后勁仍然很足。

在確定了第三種分頁方案后，我們可以據(jù)此寫一個(gè)存儲(chǔ)過程。大家知道SQL Server存儲(chǔ)過程是事先編譯好的SQL語句，它的執(zhí)行效率要比通過WEB頁面?zhèn)鱽淼腟QL語句的執(zhí)行效率要高。下面的存儲(chǔ)過程不僅含有分頁方案，還會(huì)根據(jù)頁面?zhèn)鱽淼膮?shù)來確定是否進(jìn)行數(shù)據(jù)總數(shù)統(tǒng)計(jì)。

獲取指定頁的數(shù)據(jù)：

CREATE PROCEDURE pagination3 @tblName varchar(255),  

表名 @strGetFields varchar(1000) = ''*'',需要返回的列

@fldName varchar(255)='''', 排序的字段名 @PageSize int = 10, 頁尺寸 @PageIndex int = 1, 頁碼

@doCount bit = 0, 返回記錄總數(shù), 非 0 值則返回 @OrderType bit = 0, 設(shè)置排序類型, 非 0 值則降序

@strWhere varchar(1500) = '''' 查詢條件 (注意: 不要加 where) AS declare @strSQL varchar(5000) 主語句

declare @strTmp varchar(110) 臨時(shí)變量 declare @strOrder varchar(400) 排序類型

if @doCount != 0 begin if @strWhere !='''' set @strSQL = "select count(*) as Total from   
" + @tblName + "] where "+@strWhere else set @strSQL = "select count(*) as Total from [" + @tblName + "]" end  

以上代碼的意思是如果@doCount傳遞過來的不是0，就執(zhí)行總數(shù)統(tǒng)計(jì)。以下的所有代碼都是@doCount為0的情況：

else begin if @OrderType != 0 begin set @strTmp = "<(select min" set @strOrder = " order by [" + @fldName +"] desc" 

如果@OrderType不是0，就執(zhí)行降序，這句很重要！

end else begin set @strTmp = ">(select max" set @strOrder = " order by [" + @fldName +"]   
asc" end if @PageIndex = 1 begin if @strWhere != '''' set @strSQL = "select top " + str(@PageSize) +" "+  
@strGetFields+ " from [" + @tblName + "] where " + @strWhere + " " + @strOrder else set @strSQL =   
"select top " + str(@PageSize) +" "+@strGetFields+ " from ["+ @tblName + "] "+ @strOrder  

如果是***頁就執(zhí)行以上代碼，這樣會(huì)加快執(zhí)行速度

end else begin 

以下代碼賦予了@strSQL以真正執(zhí)行的SQL代碼

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+   
" from [" + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "])  
from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from [" + @tblName +   
"]" + @strOrder + ") as tblTmp)"+ @strOrder if @strWhere != '''' set @strSQL = "select top " + str(@PageSize) +"   
"+@strGetFields+ " from [" + @tblName + "] where [" + @fldName + "]" + @strTmp + "([" + @fldName + "])   
from (select top " + str((@PageIndex-1)*@PageSize) + " [" + @fldName + "] from [" + @tblName + "] where " +  
@strWhere + " " + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder end end exec (@strSQL) GO  

上面的這個(gè)存儲(chǔ)過程是一個(gè)通用的SQL Server存儲(chǔ)過程，其注釋已寫在其中了。 在大數(shù)據(jù)量的情況下，特別是在查詢***幾頁的時(shí)候，查詢時(shí)間一般不會(huì)超過9秒；而用其他存儲(chǔ)過程，在實(shí)踐中就會(huì)導(dǎo)致超時(shí)，所以這個(gè)存儲(chǔ)過程非常適用于大容量數(shù)據(jù)庫的查詢。

筆者希望能夠通過對(duì)以上SQL Server存儲(chǔ)過程的解析，能給大家?guī)硪欢ǖ膯⑹荆⒔o工作帶來一定的效率提升，同時(shí)希望同行提出更優(yōu)秀的實(shí)時(shí)數(shù)據(jù)分頁算法。

【編輯推薦】

1. SQL Server索引選擇的引用與建議
2. SQL Server索引的正確使用標(biāo)準(zhǔn)是啥樣？
3. 三種SQL Server查找數(shù)據(jù)方法的比較
4. SQL Server 索引底層實(shí)現(xiàn)的注意事項(xiàng)
5. SQL Server 索引的底層實(shí)現(xiàn)概述