SQL Server 2005下的分页SQL

王朝mssql·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

其实基本上有三种方法:

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排序就快,按其他字段排序就慢。

大家有什么意见,欢迎拍砖。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航