SQL Server 2005下的分页SQL

其实基本上有三种方法：

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

几种写法分别如下：

 

1SELECT TOP 20 * FROM (SELECT2   ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,3   *4FROM5   dbo.mem_member) _myResults6WHERE7   RowNumber > 100008 

1SELECT * FROM (SELECT2   ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,3   *4FROM5   dbo.mem_member) _myResults6WHERE7   RowNumber between 10000 and 10020 

1WITH OrderedResults AS 23(SELECT *, ROW_NUMBER() OVER (order by Namec) as RowNumber FROM dbo.mem_member)45SELECT * 67FROM OrderedResults89WHERE 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 results10                Create Table #PageIndex11                (12                    [IndexId] int IDENTITY (1, 1) NOT NULL,13                    [Id] varchar(18) 14                )15                16                -- Insert into the temp table17                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 table26                exec sp_executesql @SQL2728                -- Return paged results29                SELECT O.*30                FROM31                    dbo.mem_member O,32                    #PageIndex PageIndex33                WHERE34                    PageIndex.IndexID > @PageLowerBound35                    AND O.[m_Id] = PageIndex.[Id]36                ORDER BY37                    PageIndex.IndexID38                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 m11WHERE m_id >= @first_id12ORDER BY m.m_id1314SET ROWCOUNT 015end不过，这种方法有缺点。按ID排序就快，按其他字段排序就慢。

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