此文章主要向大家讲述的是SQL Server 2005数据库的2种分页sql语句的比较，我们是在以下的平台和环境中对其进行比较的，即 CPU:Intel(R) Pentium(R) Dual T2390 1.86GHz 内存：1G(系统正常启动后约占300M空间) 。
硬盘：SATA 160G 8M Cache
系统：windowsxp+Sql Server 2005 sp2
测试数据：共100万条
分页测试代码：
1)row_number的两种分页方式：分别用top和between过滤
2)包含子查询结果的三种分页方式
共5种方式。
SQL Server 2005数据库的几种分页sql语句的比较方式1：
每页显示200条
分页至10万条之后的第两百条记录
Java代码 PROCEDURE [dbo].[proc_select_moauser1] AS BEGIN SET NOCOUNT ON; declare @tdiff datetime set @tdiff=getdate() select top 200 * from(select row_number() over(order by uid asc) as rownumber,* from moa_user ) as tb where rownumber>100000 select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)' END PROCEDURE [dbo].[proc_select_moauser1] AS BEGIN SET NOCOUNT ON; declare @tdiff datetime set @tdiff=getdate() select top 200 * from(select row_number() over(order by uid asc) as rownumber,* from moa_user ) as tb where rownumber>100000 select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)' END 响应时间：156ms-210ms
SQL Server 2005数据库的几种分页sql语句的比较方式2：
每页显示200条
分页至10万条之后的第两百条记录
Java代码 PROCEDURE [dbo].[proc_select_moauser2] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here declare @tdiff datetime set @tdiff=getdate() select * from(select row_number() over(order by uid asc) as rownumber,* from moa_user ) as tb where rownumber between 100000 and 100200 select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)' END PROCEDURE [dbo].[proc_select_moauser2] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here declare @tdiff datetime set @tdiff=getdate() select * from(select row_number() over(order by uid asc) as rownumber,* from moa_user ) as tb where rownumber between 100000 and 100200 select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)' END
响应时间：153ms-176ms，以上的相关内容就是对SQL Server 2005数据库的几种分页sql语句的比较的介绍，望你能有所收获。