MS-SQL分页not in 方法改进之使用row_number

上一篇博客对比了not in 和 max\min分页的效率，这次来看看row_number分页效率如何

在网上扒了一个row_number的分页存储过程，源地址：http://bbs.csdn.net/topics/300185125，在这里稍加修改，使之更加灵活

create proc Proc_TablePage
--表名
@tablename nvarchar(20),
--查询字段
@selcolumn nvarchar(1000),
--排序字段
@sortcolumn  nvarchar(255),
--每页记录数
@pagecount  int,
--页号
@pageindex  int
as
declare @beginrow int
declare @endrow int

set @beginrow=0
set @endrow=0

set @beginrow=(@pageindex-1)*@pagecount+1
set @endrow=@pageindex*@pagecount

declare @sqlstr nvarchar(4000)

set @sqlstr='with table1 as(select '+ @selcolumn +',ROW_NUMBER() OVER(ORDER BY '+ @sortcolumn+' ) AS ROW'+
            ' FROM '+@tablename+') '
set @sqlstr=@sqlstr +' SELECT '+ @selcolumn +' FROM TABLE1 WHERE ROW BETWEEN '+cast(@beginrow as nvarchar )+' and '+cast(@endrow as nvarchar)

exec(@sqlstr)

not in max\min 和row_Number三者sql：

declare @t datetime
set @t = getdate()
select top 10 ID,U_Name,U_Age,U_Sex,U_Address from Test1 where ID not in (select top 9990 ID from Test1 order by ID desc) order by ID desc 
select [not in方法降序分页执行花费时间(毫秒)]=datediff(ms,@t,getdate()) 

declare @u datetime
set @u = getdate()
exec Proc_TablePage 'Test1',' ID, U_Name,U_Age,U_Address,U_Sex ','ID desc',10,1000
select [Row_Number方法降序分页执行花费时间(毫秒)]=datediff(ms,@u,getdate())

declare @q datetime
set @q = getdate()
select top 10 ID,U_Name,U_Age,U_Sex,U_Address from Test1 where ID <=(select min(ID) from (select top 9991 ID from Test1 order by ID desc) as T) order by ID desc
select [Min方法降序分页执行花费时间(毫秒)]=datediff(ms,@q,getdate())

执行结果：

由此得出结论：三者的分页效率从高到低依次是Max\Min、row_number、not in！