SQL 分页脚本

1.Row_Number(),

count(1) over() as totalcount返回总记录数

declare @pagesize int 
declare @pagenum int

select top (@pagesize) * from (
    select top (@pagesize*@pagenum) ROW_NUMBER() over(order by columnA ) as rownum,count(1) over() as totalcount,columnB,columnC 
    from exampletableA where columnA='' 
 )exampletable 
 where rownum between (@pagenum-1)*@pagesize+1 and @pagesize*@pagenum
 order by rownum

2.offset fetch 

SQL 2012及以后的版本，可以使用如下方式分页：

declare @page int=2
declare @pagesize int=20
SELECT * FROM table1 order by kid offset (@page-1)*@pagesize rows fetch next @pagesize rows only