上一篇博客对比了not in 和 max\min分页的效率，这次来看看row_number分页效率如何
在网上扒了一个row_number的分页存储过程，源地址：，在这里稍加修改，使之更加灵活
create proc Proc_TablePage--表名@tablename nvarchar(20),--查询字段@selcolumn nvarchar(1000),--排序字段@sortcolumn nvarchar(255),--每页记录数@pagecount int,--页号@pageindex intasdeclare @beginrow intdeclare @endrow intset @beginrow=0set @endrow=0set @beginrow=(@pageindex-1)*@pagecount+1set @endrow=@pageindex*@pagecountdeclare @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 datetimeset @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 datetimeset @u = getdate()exec Proc_TablePage 'Test1',' ID, U_Name,U_Age,U_Address,U_Sex ','ID desc',10,1000select [Row_Number方法降序分页执行花费时间(毫秒)]=datediff(ms,@u,getdate())declare @q datetimeset @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 descselect [Min方法降序分页执行花费时间(毫秒)]=datediff(ms,@q,getdate())
由此得出结论：三者的分页效率从高到低依次是Max\Min、row_number、not in！