其實基本上有三種方法：

1、使用SQL Server 2005中新增的ROW_NUMBER

幾種寫法分別如下：

1SELECT?TOP?20?*?FROM?(SELECT
2???ROW_NUMBER()?OVER?(ORDER?BY?Namec)?AS?RowNumber,
3???*
4FROM
5???dbo.mem_member)?_myResults
6WHERE
7???RowNumber?>?10000
8

?

1SELECT?*?FROM?(SELECT
2???ROW_NUMBER()?OVER?(ORDER?BY?Namec)?AS?RowNumber,
3???*
4FROM
5???dbo.mem_member)?_myResults
6WHERE
7???RowNumber?between?10000?and?10020

?

1WITH?OrderedResults?AS?
2
3(SELECT?*,?ROW_NUMBER()?OVER?(order?by?Namec)?as?RowNumber?FROM?dbo.mem_member)
4
5SELECT?*?
6
7FROM?OrderedResults
8
9WHERE?RowNumber?between?10000?and?10020

不管哪種寫法，性能都不理想。在8,9萬條數據的情況下要運行6秒左右。


2、使用臨時表再加存儲過程

?1BEGIN
?2????????????????DECLARE?@PageLowerBound?int
?3????????????????DECLARE?@PageUpperBound?int
?4????????????????
?5????????????????--?Set?the?page?bounds
?6????????????????SET?@PageLowerBound?=?10000
?7????????????????SET?@PageUpperBound?=?10020
?8
?9????????????????--?Create?a?temp?table?to?store?the?select?results
10????????????????Create?Table?#PageIndex
11????????????????(
12????????????????????[IndexId]?int?IDENTITY?(1,?1)?NOT?NULL,
13????????????????????[Id]?varchar(18)?
14????????????????)
15????????????????
16????????????????--?Insert?into?the?temp?table
17????????????????declare?@SQL?as?nvarchar(4000)
18????????????????SET?@SQL?=?'INSERT?INTO?#PageIndex?(Id)'
19????????????????SET?@SQL?=?@SQL?+?'?SELECT'
20????????????????SET?@SQL?=?@SQL?+?'?TOP?'?+?convert(nvarchar,?@PageUpperBound)
21????????????????SET?@SQL?=?@SQL?+?'?m_id'
22????????????????SET?@SQL?=?@SQL?+?'?FROM?dbo.mem_member'
23????????????????SET?@SQL?=?@SQL?+?'?ORDER?BY?NameC'
24????????????????
25????????????????--?Populate?the?temp?table
26????????????????exec?sp_executesql?@SQL
27
28????????????????--?Return?paged?results
29????????????????SELECT?O.*
30????????????????FROM
31????????????????????dbo.mem_member?O,
32????????????????????#PageIndex?PageIndex
33????????????????WHERE
34????????????????????PageIndex.IndexID?>?@PageLowerBound
35????????????????????AND?O.[m_Id]?=?PageIndex.[Id]
36????????????????ORDER?BY
37????????????????????PageIndex.IndexID
38????????????????
39drop?table?#PageIndex????????????
40????????????????END

而使用這種方法，在同樣的情況下用時只需1秒。

看樣子，row_number是個雞肋。

3、如果覺得臨時表不好，還可以使用SET ROWCOUNT

?1begin
?2DECLARE?@first_id?varchar(18),?@startRow?int
?3????
?4SET?ROWCOUNT?10000
?5SELECT?@first_id?=?m_id?FROM?mem_member?ORDER?BY?m_id
?6
?7SET?ROWCOUNT?20
?8
?9SELECT?m.*?
10FROM?mem_member?m
11WHERE?m_id?>=?@first_id
12ORDER?BY?m.m_id
13
14SET?ROWCOUNT?0
15end

不過，這種方法有缺點。按ID排序就快，按其他字段排序就慢。

大家有什么意見，歡迎拍磚。

參考文章：

http://www.4guysfromrolla.com/webtech/042606-1.shtml

http://www.4guysfromrolla.com/webtech/041206-1.shtml

http://www.singingeels.com/Articles/Pagination_In_SQL_Server_2005.aspx

轉載于:https://www.cnblogs.com/didasoft/archive/2007/07/20/SqlServer-Paging.html

總結

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

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