SQL2005 ROW_NUMBER() 的性能如何？

Question

以下是别人的说法：SQL Server 2005之后，令人困扰的分页问题似乎有了进展，那就是用ROW_NUMBER()，典型的语句如下SELECT * 
FROM
(
SELECT *,ROW_NUMBER() OVER (ORDER BY XXX) AS RowNo 
FROM tbl
) AS A
WHERE RowNo >= 11 and RowNo <= 20; 
但这里忽略了一个重要的问题，那就是中间那个SELECT语句，实际上相当于做了一个SELECT *，虽然没在结果集看到，但没看到不表示数据库没做。
为什么看起来查询很快，其实是因为后面的RowNo的范围，网上流传的示例RowNo都较小，所以看不出问题，在上例中，SQL Server发现后面的条件之后，中间的SELECT被作了类似TOP 20的处理，也就是只取了20行，所以查询非常快。但把RowNo的查询范围改为10000000到1000010之后，则相当于先TOP 10万行，这时速度就明显慢下来了。
所以说，这个表达式的本质只是先TOP X，再选出X-y,X这个小范围的行，相比以前并没有什么进步。
我想问的是，ROW_NUMBER() 真的性能不那么好吗？ROW_NUMBER()是不是用到了临时表？

Answer 1

WHERE RowNo >= 11 and RowNo <= 20取n到m条记录的语句1.
select top m * from tablename where id not in (select top n id from tablename)2.
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入
set rowcount n
select * from 表变量 order by columnname desc3.
select top n * from 
(select top m * from tablename order by columnname) a
order by columnname desc
4.如果tablename里没有其他identity列，那么：
select identity(int) id0,* into #temp from tablename取n到m条的语句为：
select * from #temp where id0 >=n and id0 <= m如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行：
exec sp_dboption 你的DB名字,'select into/bulkcopy',true
5.如果表里有identity属性，那么简单：
select * from tablename where identitycol between n and m 如果是sql server 2005 可以这样写： 
select top 20 * from T order col
except
select top 2 * from T order col

Answer 2

我想问的是，ROW_NUMBER() 真的性能不那么好吗？ROW_NUMBER()是不是用到了临时表？ 
－－－－－－－－
我测试的结果是ROW_NUMBER()性能很好，分页的时候比用TOP或临时表快。

Answer 3

--方法1：
--适用于 SQL Server 2000/2005
SELECT TOP 页大小 *
FROM table1
WHERE id NOT IN
          (
          SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
          )
ORDER BY id
--方法2：
--适用于 SQL Server 2000/2005
SELECT TOP 页大小 *
FROM table1
WHERE id >
          (
          SELECT ISNULL(MAX(id),0) 
          FROM 
                (
                SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
                ) A
          )
ORDER BY id
--方法3：
--适用于 SQL Server 2005
SELECT TOP 页大小 * 
FROM 
        (
        SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
        ) A
WHERE RowNumber > 页大小*(页数-1)
--方法四 适用于 SQL Server 2005
DECLARE @pagenum AS INT, @pagesize AS INT 
SET @pagenum = 2 
SET @pagesize = 3 
SELECT * 
FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum, 
        newsid, topic, ntime, hits 
      FROM news) AS D 
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize 
ORDER BY newsid DESC
说明，页大小：每页的行数；页数：第几页。使用时，请把“页大小”和“页大小*(页数-1)”替换成数字。

Answer 4

数据越往后就越慢,还是top的方法比较快

Answer 5

联想ThinkPad SL410（28428KC）

Answer 6

对40万条记录的表与2万条记录的两个表inner join 联合查询，应用
SELECT TOP 页大小 * 
FROM 
        (
        SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
        ) A
WHERE RowNumber > 页大小*(页数-1)试用结果：比top有很大性能提升，翻页到1万页（10万条）不再死掉，但4万页（40万）左右开始，照样经常死掉。
修改查询语句为如下形式，也是一样
SELECT TOP 页大小 * 
FROM 
        (
        SELECT top 页大小*(页数) ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
        ) A
WHERE RowNumber > 页大小*(页数-1)

Answer 7

修改查询语句为如下形式，也是一样SQL code    SELECT TOP 页大小 * FROM ( SELECT top 页大小*(页数) ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,col1,col2,col3,col4 FROM table1 ) A WHERE RowNumber > 页大小*(页数-1)