SQL2005基于ROW_NUMBER() 两种分页性能比

 

相比在SQL Server 2000 中使用的分页方式，在SQL Server 2005中使用新的语法ROW_NUMBER()来分页效率要高出很多，但是很多人在使用ROW_NUMBER()这种分页方式时，使用的方法并不正确，以下列出不正确的和正确的做法并做简单分析：

首先假设我们已经创建了如下的表和索引并初始化了100万条数据：

CREATE TABLE [dbo].[Users]
(
　　[ID] [int] IDENTITY(1,1) NOT NULL,
　　[Name] [varchar](50) NULL,
　　[test] [nchar](10) NULL,
　　CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
　　(
 　　　　[ID] ASC
　　) ON [PRIMARY]
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [Inx_Name] ON [dbo].[Users]
(
　　[Name] ASC
) ON [PRIMARY]

DECLARE @index INT
SET @index=0
WHILE @index<1000000
BEGIN
        INSERT INTO Users(Name,test) values(@index,'walkingp')
  SET @index = @index + 1
END 

不正确的使用方式(查出所有数据后再排序)：

select Id,Name,test from (select row_number() over(order by name) as rowNum,* from users) as t where rowNum between 5000 and 5100 

正确的使用方式如下(查出主键进行排序过滤，然后使用过滤后的主键来查找数据)：

select a.Id,a.Name,a.test  from users as a inner join (select rowNum,id from (select row_number() over(order by name) as rowNum,ID from users) as t where rowNum between 4000 and 4100) as b on a.id = b.id order by b.rownum

以下具体分析：

set statistics io on
set statistics time on

go
print 'Error.5000-------------------------'
declare @time datetime 
declare @ms int
set @time= getdate() 

select Id,Name,test from (select row_number() over(order by name) as rowNum,* from users) as t where rowNum between 5000 and 5100 

set @ms=datediff(ms,@time,getdate()) 
print @ms--毫秒数

go
print 'Right.5000-------------------------'
declare @time datetime 
declare @ms int
set @time= getdate() 

select a.Id,a.Name,a.test  from users as a inner join (select rowNum,id from (select row_number() over(order by name) as rowNum,ID from users) as t where rowNum between 5000 and 5100) as b on a.id = b.id order by b.rownum

set @ms=datediff(ms,@time,getdate()) 
print @ms--毫秒数

go