SQL Server 2005-2008 ROW_NUMBER() 分页函数效率

-- 测试数据量：2161852 条
declare @i datetime

set @i = GETDATE();
--SQL 2005 -2008
-- 开始
WITH temp AS (SELECT id,title,body, ROW_NUMBER() OVER (ORDER BY id) AS 'RowNumber' FROM Article)
SELECT * FROM temp
WHERE RowNumber BETWEEN 5000 AND 5010
-- 结束
print '耗时'+cast(datediff(ms,@i,getdate()) as varchar(10));  -- (11 行受影响)耗时3606

set @i = GETDATE();
--SQL 2000
-- 开始
select top 11 * from Article where id not in (select top 4999 id from Article order by id) order by id
-- 结束
print '耗时'+cast(datediff(ms,@i,getdate()) as varchar(10)); -- (11 行受影响) 耗时22156
 

官方说明：​​http://msdn.microsoft.com/en-us/library/ms186734.aspx​​