SQL Server 2005下的分頁SQL

< DOCTYPE html PUBLIC -WCDTD XHTML TransitionalEN httpwwwworgTRxhtmlDTDxhtml-transitionaldtd>

其實基本上有三種方法：

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

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

9WHERE RowNumber between 10000 and 10020

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

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

1BEGIN

2 DECLARE @PageLowerBound int

3 DECLARE @PageUpperBound int

5 -- Set the page bounds

6 SET @PageLowerBound = 10000

7 SET @PageUpperBound = 10020

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

7SET ROWCOUNT 20

9SELECT m.*

10FROM mem_member m

11WHERE m_id >= @first_id

12ORDER BY m.m_id

13

14SET ROWCOUNT 0

15end

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

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

參考文章：

<a target="_blank" href="http://www.4guysfromrolla.com/webtech/042606-1.shtml">http://www.4guysfromrolla.com/webtech/042606-1.shtml</a>

<a target="_blank" href="http://www.4guysfromrolla.com/webtech/041206-1.shtml">http://www.4guysfromrolla.com/webtech/041206-1.shtml</a>

<a target="_blank" href="http://www.singingeels.com/Articles/Pagination_In_SQL_Server_2005.aspx">http://www.singingeels.com/Articles/Pagination_In_SQL_Server_2005.aspx</a>

本文轉自 netcorner 部落格園部落格，原文連結：http://www.cnblogs.com/netcorner/archive/2007/09/25/2912289.html  ，如需轉載請自行聯系原作者