sqlserver 分页 row_number() over(), offset fetch next only

1- row_number() over()  

declare @pageIndex int=1,@pageSize=10
select * from (
    select ROW_NUMBER() over(order by Id ) 'rowid',count(*) over() 'TotalCount',* from LandHouse 
) as tab 
where rowid between (pageIndex -1)*pageSize and  pageIndex *pageSize

--pageIndex  当前页  pageSize 每页数量

2- offset  fetch next   only (sqlserver 版本>=2012)

declare @pageIndex int =1,@pageSize int =10
select count(*) over() 'TotalCount',* from LandHouse 
where 1=1
order by Id asc --/desc
offset(@pageIndex-1)*@pageSize rows fetch next @pageSize rows only

--pageIndex  当前页  pageSize  每页数量
--注   必须 order by 后才能使用 offset 分页