sqlserver 分页

采用row_number()比offset fetch分页效率低，

数据量在几万时差别不大，十万级的时候效率差别逐渐拉开

一百万零十八条数据,

• 第一种采用row_number()存储过程查询

  • 调存储过程

  ALTER PROCEDURE [dbo].[page_depart](@index int,@size int)
  AS
  BEGIN
  	-- SET NOCOUNT ON added to prevent extra result sets from
  	-- interfering with SELECT statements.
  	SET NOCOUNT ON;
  	declare @start int,@end int
  	set @start = (@index-1)*@size+1
  	set @end = @start+@size-1
      -- Insert statements for procedure here
  	SELECT * from
  	(select *,ROW_NUMBER() over(order by Id asc) as number  from [User]   ) temp
  	where temp.number between @start and @end 
  	-- select count(id) as sum  from [User]  
  END
  

  每页十条，查询第十万页，1000001条-1000010条

  大概300多毫秒

• 直接执行语句 效率不变

  SELECT * from
  	(select *,ROW_NUMBER() over(order by Id asc) as number  from [User]   ) temp
  	where temp.number between 1000001 and 1000010 
  

• 采用 offset fetch语句查询

  select * from [user]
  order by id asc
  offset (1000000) rows
  fetch next 10 rows only
  

  查询同样的页数

  耗时大概90多毫秒