SQL Server 2005下的分页SQL

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

几种写法分别如下：

SELECT TOP 20 * FROM (SELECT
   ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,
   *
FROM
   dbo.mem_member) _myResults
WHERE
   RowNumber > 10000

 

 

SELECT * FROM (SELECT
   ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,
   *
FROM
   dbo.mem_member) _myResults
WHERE
  RowNumber between 10000 and 10020

 

 

WITH OrderedResults AS 

(SELECT *, ROW_NUMBER() OVER (order by Namec) as RowNumber FROM dbo.mem_member)

SELECT * 

FROM OrderedResults

WHERE 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
不过，这种方法有缺点。按ID排序就快，按其他字段排序就慢。

 

begin
 DECLARE @first_id varchar(18), @startRow int
   
 SET ROWCOUNT 10000
 SELECT @first_id = m_id FROM mem_member ORDER BY m_id
 
 SET ROWCOUNT 20
 
 SELECT m.* 
FROM mem_member m
WHERE m_id >= @first_id
ORDER BY m.m_id

SET ROWCOUNT 0
end