使用SQL Server 2005 新的语法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

 

 

　错误的使用方式逻辑读要比正确的使用方式的逻辑读大的多，而且页码越大读的越多，最终导致效率越来越差，这点也可以通过执行计划看出端倪。

以下是执行计划：

　　通过对比执行计划我们发现错误的使用方式在一开始就要读取聚集索引的数据分页中的数据，而正确的使用方式在一开始只是读取Inx_Name 所引的所有数据分析，这在最后查出 101 条数据后才从聚集索引的数据分页中查找数据，因此效率要高的多，这种方式应该是创建此类 SQL 的一个通用原则。