其實(shí)基本上有三種方法：

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

幾種寫(xiě)法分別如下：

1SELECT TOP 20 * FROM (SELECT2ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,3*4FROM5dbo.mem_member) _myResults6WHERE7RowNumber > 100008

1SELECT * FROM (SELECT2ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,3*4FROM5dbo.mem_member) _myResults6WHERE7RowNumber 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不管哪種寫(xiě)法，性能都不理想。在8,9萬(wàn)條數(shù)據(jù)的情況下要運(yùn)行6秒左右。

2、使用臨時(shí)表再加存儲(chǔ)過(guò)程

1BEGIN2;;;;;DECLARE @PageLowerBound int3;;;;;DECLARE @PageUpperBound int4;;;;;5;;;;;-- Set the page bounds6;;;;;SET @PageLowerBound = 100007;;;;;SET @PageUpperBound = 1002089;;;;;-- 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而使用這種方法，在同樣的情況下用時(shí)只需1秒。

看樣子，row_number是個(gè)雞肋。

3、如果覺(jué)得臨時(shí)表不好，還可以使用SET ROWCOUNT

1begin2DECLARE @first_id varchar(18), @startRow int3;4SET ROWCOUNT 100005SELECT @first_id = m_id FROM mem_member ORDER BY m_id67SET ROWCOUNT 2089SELECT m.* 10FROM mem_member m11WHERE m_id >= @first_id12ORDER BY m.m_id1314SET ROWCOUNT 015end不過(guò)，這種方法有缺點(diǎn)。按ID排序就快，按其他字段排序就慢。

大家有什么意見(jiàn)，歡迎拍磚。