相比在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