MS SQL 三种分页

sqlserver 常见的几种分页代码。row_number() sqlserver 2005、sqlserver2008，兼容高效。

更高版本的sqlserver可能会路线更新，加入新的性能更好的分页方式。
------ row number ----------

------ row number ----------

declare @pageSize int,@pageIndex int
set @pageSize = 20;set @pageIndex = 2
select * from (
SELECT ROW_NUMBER()  OVER(ORDER BY Byod_ZZFP.ID DESC) as rowID
,Byod_ZZFP.* from Byod_ZZFP  where 1=1 
)T  
where (rowID > @pageSize * (@pageIndex - 1)  and rowID <=  @pageSize * (@pageIndex)) order by ID desc 

-- CTE表达式 --
declare @pageSize int,@pageIndex int
set @pageSize = 20;set @pageIndex = 2;
 
with T as
(
  SELECT ROW_NUMBER()  OVER(ORDER BY Byod_ZZFP.ID DESC) as rowID
  ,Byod_ZZFP.* from Byod_ZZFP  where 1=1   
)
select * from T
where (rowID > @pageSize * (@pageIndex - 1)  and rowID <=  @pageSize * (@pageIndex)) order by ID desc 

------ max/min ----------

CREATE PROC [dbo].[uspLGetSolutionList]
@pageSize INT, --页码大小 
@pageIndex INT, --页码
@strWhere nvarchar(2000)='',
@totalRecordCount INT OUTPUT --总记录数

AS
DECLARE @strSql NVARCHAR(MAX) --sql语句
DECLARE @sqlcount INT    --返回总记录
DECLARE @strSqlCount NVARCHAR(MAX) --sql语句1,总记录数语句
DECLARE @tempSql NVARCHAR(MAX) --查询字段
DECLARE @temTableOn NVARCHAR(2000) --表连接及表连接关系
DECLARE @order NVARCHAR(500) --排序

SET @tempSql = ' NetworkCutover.ID '
SET @temTableOn = ' NetworkCutover NetworkCutover
inner join Member Member on NetworkCutover.DutyMember = Member.MemberID 
 '
SET @strSql = ' ';
SET @order = ' ORDER BY NetworkCutover.ID desc '
IF(@pageIndex <= 1)
  BEGIN
    SET @strSql = 'SELECT TOP '+STR(@pageSize) + @tempSql +' FROM ' + @temTableOn + ' WHERE 1=1 ' + @strWhere + @order;
  END
ELSE
  BEGIN
    SET @strSql = 'SELECT TOP '+STR(@pageSize)+@tempSql +' FROM '+@temTableOn +' WHERE NetworkCutover.ID < (SELECT MIN(T.ID) FROM (SELECT TOP '+STR(@pageSize*(@pageIndex-1))+' NetworkCutover.ID FROM '+@temTableOn+' WHERE 1=1 '+@strWhere +@order +') T)'+@strWhere+ @order
  END
  print @strSql
  SET @strSqlCount='SELECT @sqlcount=COUNT(*) FROM '+@temTableOn+' WHERE 1=1 '+@strWhere
  print @strSqlCount
EXEC SP_EXECUTESQL @strSqlCount,N'@sqlcount INT OUTPUT',@sqlcount OUTPUT
  SET @totalRecordCount=@sqlcount
  EXEC(@strSql)
  
GO

  小技巧：

可增加一个总记录数作为输入参数，查询条件不变的情况，总记录数不变（不再查询总记录数）。第一次查询输入参数为0需要查询总记录数。

从sqlserver 2012开始加入了新的分页

OFFSET  FETCH NEXT

DECLARE @page INT,
        @size INT;
SELECT @page = 1,
       @size = 30;

SELECT --COUNT(1) OVER (PARTITION BY '') AS Total,
    --ROW_NUMBER() OVER (ORDER BY Pid) AS rowid,
    a.*,
    b.UserName
FROM dbo.KaQuan a WITH (NOLOCK)
    INNER JOIN dbo.PiCi b WITH (NOLOCK)
        ON a.PiCi_SNID = b.SNID
WHERE 1 = 1
ORDER BY a.Pid OFFSET (@page - 1) * @size ROWS FETCH NEXT @size ROWS ONLY;

个人测试经验：在相同实例下，相同的业务查询，在sqlserver 2012环境下。使用row_number 和offset方式，区别不大，测试结果是row_number更快。但是网上很多人都说新的offset方式性能更好。不解。。。。

开窗函数还是挺耗性能的。

COUNT(1) OVER (PARTITION BY '') AS Total 这里返回总行数。还有一种就是查询2次啦。