其实基本上有三种方法：

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

几种写法分别如下：

1SELECT TOP 20 * FROM (SELECT
2ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,
3*
4FROM
5dbo.mem_member) _myResults
6WHERE
7RowNumber > 10000
8

1SELECT * FROM (SELECT
2ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,
3*
4FROM
5dbo.mem_member) _myResults
6WHERE
7RowNumber 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排序就快，按其他字段排序就慢。

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