SQL Server 2005下的分页SQL (转载)
SQL Server 2005下的分页SQL
其实基本上有三种方法:
1、使用SQL Server 2005中新增的ROW_NUMBER
几种写法分别如下:
1
SELECT TOP 20 * FROM (SELECT
2
ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,
3
*
4
FROM
5
dbo.mem_member) _myResults
6
WHERE
7
RowNumber > 10000
8![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
1
SELECT * FROM (SELECT
2
ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,
3
*
4
FROM
5
dbo.mem_member) _myResults
6
WHERE
7
RowNumber between 10000 and 10020
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
1
WITH OrderedResults AS
2![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
(SELECT *, ROW_NUMBER() OVER (order by Namec) as RowNumber FROM dbo.mem_member)
4![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
SELECT *
6![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
FROM OrderedResults
8![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
WHERE RowNumber between 10000 and 10020
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
不管哪种写法,性能都不理想。在8,9万条数据的情况下要运行6秒左右。
2、使用临时表再加存储过程
1
BEGIN
2
DECLARE @PageLowerBound int
3
DECLARE @PageUpperBound int
4
5
-- Set the page bounds
6
SET @PageLowerBound = 10000
7
SET @PageUpperBound = 10020
8![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
39
drop table #PageIndex
40
END
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
而使用这种方法,在同样的情况下用时只需1秒。
看样子,row_number是个鸡肋。
3、如果觉得临时表不好,还可以使用SET ROWCOUNT
1
begin
2
DECLARE @first_id varchar(18), @startRow int
3
4
SET ROWCOUNT 10000
5
SELECT @first_id = m_id FROM mem_member ORDER BY m_id
6![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
SET ROWCOUNT 20
8![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
SELECT m.*
10
FROM mem_member m
11
WHERE m_id >= @first_id
12
ORDER BY m.m_id
13![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
SET ROWCOUNT 0
15
end
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
不过,这种方法有缺点。按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