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

1- row_number() over()  

1 declare @pageIndex int=1,@pageSize=10
2 select * from (
3     select ROW_NUMBER() over(order by Id ) 'rowid',count(*) over() 'TotalCount',* from LandHouse 
4 ) as tab 
5 where rowid between (pageIndex -1)*pageSize and  pageIndex *pageSize
6 
7 --pageIndex  当前页  pageSize 每页数量

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

1 declare @pageIndex int =1,@pageSize int =10
2 select count(*) over() 'TotalCount',* from LandHouse 
3 where 1=1
4 order by Id asc --/desc
5 offset(@pageIndex-1)*@pageSize rows fetch next @pageSize rows only
6 
7 --pageIndex  当前页  pageSize  每页数量
8 --注   必须 order by 后才能使用 offset 分页