Sqlserver數據庫分頁查詢一直是Sqlserver的短板，閑來無事，想出幾種方法，假設有表ARTICLE,字段ID、YEAR...(其他省略)，數據53210條(客戶真實數據，量不大)，分頁查詢每頁30條，查詢第1500頁（即第45001-45030條數據），字段ID聚集索引，YEAR無索引，Sqlserver版本：2008R2

第一種方案、最簡單、普通的方法：

代碼如下: [sql]?view plaincopy

SELECT?TOP?30?*?FROM?ARTICLE?WHERE?ID?NOT?IN(SELECT?TOP?45000?ID?FROM?ARTICLE?ORDER?BY?YEAR?DESC,?ID?DESC)?ORDER?BY?YEAR?DESC,ID?DESC??

???? 平均查詢100次所需時間：45s

第二種方案：

代碼如下:
[sql]?view plaincopy

SELECT?*?FROM?(　　SELECT?TOP?30?*?FROM?(SELECT?TOP?45030?*?FROM?ARTICLE?ORDER?BY?YEAR?DESC,?ID?DESC)?f?ORDER?BY?f.YEAR?ASC,?f.ID?DESC)?s?ORDER?BY?s.YEAR?DESC,s.ID?DESC???

???? 平均查詢100次所需時間：138S

第三種方案：

代碼如下:
[sql]?view plaincopy

SELECT?*?FROM?ARTICLE?w1,???

(??

????SELECT?TOP?30?ID?FROM???

????(??

????????SELECT?TOP?50030?ID,?YEAR?FROM?ARTICLE?ORDER?BY?YEAR?DESC,?ID?DESC??

????)?w?ORDER?BY?w.YEAR?ASC,?w.ID?ASC??

)?w2?WHERE?w1.ID?=?w2.ID?ORDER?BY?w1.YEAR?DESC,?w1.ID?DESC??

???? 平均查詢100次所需時間：21S

第四種方案：

代碼如下: [sql]?view plaincopy

SELECT?*?FROM?ARTICLE?w1???

????WHERE?ID?in???

????????(??

????????????SELECT?top?30?ID?FROM???

????????????(??

????????????????SELECT?top?45030?ID,?YEAR?FROM?ARTICLE?ORDER?BY?YEAR?DESC,?ID?DESC??

????????????)?w?ORDER?BY?w.YEAR?ASC,?w.ID?ASC??

????????)???

????ORDER?BY?w1.YEAR?DESC,?w1.ID?DESC??

???? 平均查詢100次所需時間：20S

第五種方案：

代碼如下:
[sql]?view plaincopy

SELECT?w2.n,?w1.*?FROM?ARTICLE?w1,?(　　SELECT?TOP?50030?row_number()?OVER?(ORDER?BY?YEAR?DESC,?ID?DESC)?n,?ID?FROM?ARTICLE?)?w2?WHERE?w1.ID?=?w2.ID?AND?w2.n?>?50000?ORDER?BY?w2.n?ASC???

???? 平均查詢100次所需時間：15S

查詢第1000-1030條記錄

第一種方案：

代碼如下:
[sql]?view plaincopy

SELECT?TOP?30?*?FROM?ARTICLE?WHERE?ID?NOT?IN(SELECT?TOP?1000?ID?FROM?ARTICLE?ORDER?BY?YEAR?DESC,?ID?DESC)?ORDER?BY?YEAR?DESC,ID?DESC???

???? 平均查詢100次所需時間：80s

第二種方案：

代碼如下:
[sql]?view plaincopy

SELECT?*?FROM??(?　　SELECT?TOP?30?*?FROM?(SELECT?TOP?1030?*?FROM?ARTICLE?ORDER?BY?YEAR?DESC,?ID?DESC)?f?ORDER?BY?f.YEAR?ASC,?f.ID?DESC)?s?ORDER?BY?s.YEAR?DESC,s.ID?DESC???

???? 平均查詢100次所需時間：30S

第三種方案：

代碼如下: [sql]?view plaincopy

SELECT?*?FROM?ARTICLE?w1,???

(??

????SELECT?TOP?30?ID?FROM???

????(??

????????SELECT?TOP?1030?ID,?YEAR?FROM?ARTICLE?ORDER?BY?YEAR?DESC,?ID?DESC??

????)?w?ORDER?BY?w.YEAR?ASC,?w.ID?ASC??

)?w2?WHERE?w1.ID?=?w2.ID?ORDER?BY?w1.YEAR?DESC,?w1.ID?DESC??

???? 平均查詢100次所需時間：12S

第四種方案：

代碼如下: [sql]?view plaincopy

SELECT?*?FROM?ARTICLE?w1???

????WHERE?ID?in???

????????(??

????????????SELECT?top?30?ID?FROM???

????????????(??

????????????????SELECT?top?1030?ID,?YEAR?FROM?ARTICLE?ORDER?BY?YEAR?DESC,?ID?DESC??

????????????)?w?ORDER?BY?w.YEAR?ASC,?w.ID?ASC??

????????)???

????ORDER?BY?w1.YEAR?DESC,?w1.ID?DESC??

???? 平均查詢100次所需時間：13S

第五種方案：

代碼如下:
[sql]?view plaincopy

SELECT?w2.n,?w1.*?FROM?ARTICLE?w1,(?　　SELECT?TOP?1030?row_number()?OVER?(ORDER?BY?YEAR?DESC,?ID?DESC)?n,?ID?FROM?ARTICLE)?w2?WHERE?w1.ID?=?w2.ID?AND?w2.n?>?1000?ORDER?BY?w2.n?ASC???

???? 平均查詢100次所需時間：14S

???? 由此可見在查詢頁數靠前時，效率3>4>5>2>1，頁碼靠后時5>4>3>1>2，再根據用戶習慣，一般用戶的檢索只看最前面幾頁，因此選擇3 4 5方案均可，若綜合考慮方案5是最好的選擇，但是要注意SQL2000不支持row_number()函數，由于時間和條件的限制沒有做更深入、范圍更廣的測試，有興趣的可以仔細研究下。

以下是根據第四種方案編寫的一個分頁存儲過程：

代碼如下:

[sql]?view plaincopy

if?exists?(select?*?from?dbo.sysobjects?where?id?=?object_id(N'[dbo].[sys_Page_v2]')?and?OBJECTPROPERTY(id,?N'IsProcedure')?=?1)??

drop?procedure?[dbo].[sys_Page_v2]??

GO??

CREATE?PROCEDURE?[dbo].[sys_Page_v2]??

@PCount?int?output,????--總頁數輸出??

@RCount?int?output,????--總記錄數輸出??

@sys_Table?nvarchar(100),????--查詢表名??

@sys_Key?varchar(50),????????--主鍵??

@sys_Fields?nvarchar(500),????--查詢字段??

@sys_Where?nvarchar(3000),????--查詢條件??

@sys_Order?nvarchar(100),????--排序字段??

@sys_Begin?int,????????--開始位置??

@sys_PageIndex?int,????????--當前頁數??

@sys_PageSize?int????????--頁大小??

AS??

SET?NOCOUNT?ON??

SET?ANSI_WARNINGS?ON??

IF?@sys_PageSize?<?0?OR?@sys_PageIndex?<?0??

BEGIN??????????

RETURN??

END??

DECLARE?@new_where1?NVARCHAR(3000)??

DECLARE?@new_order1?NVARCHAR(100)??

DECLARE?@new_order2?NVARCHAR(100)??

DECLARE?@Sql?NVARCHAR(4000)??

DECLARE?@SqlCount?NVARCHAR(4000)??

DECLARE?@Top?int??

if(@sys_Begin?<=0)??

????set?@sys_Begin=0??

else??

????set?@sys_Begin=@sys_Begin-1??

IF?ISNULL(@sys_Where,'')?=?''??

????SET?@new_where1?=?'?'??

ELSE??

????SET?@new_where1?=?'?WHERE?'?+?@sys_Where??

IF?ISNULL(@sys_Order,'')?<>?''???

BEGIN??

????SET?@new_order1?=?'?ORDER?BY?'?+?Replace(@sys_Order,'desc','')??

????SET?@new_order1?=?Replace(@new_order1,'asc','desc')??

????SET?@new_order2?=?'?ORDER?BY?'?+?@sys_Order??

END??

ELSE??

BEGIN??

????SET?@new_order1?=?'?ORDER?BY?ID?DESC'??

????SET?@new_order2?=?'?ORDER?BY?ID?ASC'??

END??

SET?@SqlCount?=?'SELECT?@RCount=COUNT(1),@PCount=CEILING((COUNT(1)+0.0)/'??

????????????+?CAST(@sys_PageSize?AS?NVARCHAR)+')?FROM?'?+?@sys_Table?+?@new_where1??

EXEC?SP_EXECUTESQL?@SqlCount,N'@RCount?INT?OUTPUT,@PCount?INT?OUTPUT',??

???????????????@RCount?OUTPUT,@PCount?OUTPUT??

IF?@sys_PageIndex?>?CEILING((@RCount+0.0)/@sys_PageSize)????--如果輸入的當前頁數大于實際總頁數,則把實際總頁數賦值給當前頁數??

BEGIN??

????SET?@sys_PageIndex?=??CEILING((@RCount+0.0)/@sys_PageSize)??

END??

set?@sql?=?'select?'+?@sys_fields?+'?from?'?+?@sys_Table?+?'?w1?'??

????+?'?where?'+?@sys_Key?+'?in?('??

????????+'select?top?'+?ltrim(str(@sys_PageSize))?+'?'?+?@sys_Key?+?'?from?'??

????????+'('??

????????????+'select?top?'?+?ltrim(STR(@sys_PageSize?*?@sys_PageIndex?+?@sys_Begin))?+?'?'?+?@sys_Key?+?'?FROM?'??

????????+?@sys_Table?+?@new_where1?+?@new_order2???

????????+')?w?'?+?@new_order1??

????+')?'?+?@new_order2??

print(@sql)??

Exec(@sql)??

GO ?

文章來源：CSDN 作者：語不驚人死不休 查看原文

總結

以上是生活随笔為你收集整理的高效的SQLSERVER分页查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。