WEB开发网
开发学院数据库MSSQL Server SQL Server 2005下的分页SQL 阅读

SQL Server 2005下的分页SQL

 2007-11-11 12:01:57 来源:WEB开发网   
核心提示:其实基本上有三种方法:1、使用sql server(WINDOWS平台上强大的数据库平台) 2005中新增的ROW_NUMBER几种写法分别如下:1SELECT TOP 20 * FROM (SELECT2 ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,3 *4FROM

其实基本上有三种方法:

1、使用sql server(WINDOWS平台上强大的数据库平台) 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排序就快,按其他字段排序就慢。

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

Tags:SQL Server SQL

编辑录入:coldstar [复制链接] [打 印]
赞助商链接