作为程序员来说，与数据库打交道是十分频繁的分页查询是一个开拓者必须节制的基本知识点，目前整理了下面三种SQLServer分页查询语句的写法，仅供参考。
一、Top Not IN 办法（查询靠前的数据较快）语法格式：
SELECTtop条数fromtablenamewhereIdnotin(selecttoppageSize(pageIndex-1) Idfromtablename)
示例：
SELECT TOP 2 FROM Users WHERE Id NOT IN (SELECT TOP 2Id FROM Users)二、ROW_NUMBER() OVER()办法 （查询靠后的数据速率较快）
语法格式：
SELECTFROM(SELECT,ROW_NUMBER()OVER(OrderbyId)ASRowNumberfromtablename )asb where RowNumberbetween pageIndex-1pageSizeandpageIndexpageSize
示例：
SELECT FROM ( SELECT , ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber FROM Users ) as b where RowNumber BETWEEN 0 and 5三、offset fetch next办法 （速率更优，限定Sql2012版本以上可以利用）
语法格式：
select from tablename order by Id offset pageIndexrow fetch next pageSize row only
示例：
select from Users order by Id offset 2 row fetch next 5 row only 四、分页存储过程
create PROCEDURE GetPageData(@TableName varchar(30),--表名称@IDName varchar(20),--表主键名称@PageIndex int,--当前页数@PageSize int--每页大小)ASIF @PageIndex > 0BEGINset nocount onDECLARE @PageLowerBound int,@StartID int,@sql nvarchar(225)SET @PageLowerBound = @PageSize (@PageIndex-1)IF @PageLowerBound<1SET @PageLowerBound=1SET ROWCOUNT @PageLowerBoundSET @sql=N'SELECT @StartID = ['+@IDName+'] FROM '+@TableName+' ORDER BY '+@IDNameexec sp_executesql @sql,N'@StartID int output',@StartID outputSET ROWCOUNT 0SET @sql='select top '+str(@PageSize) +' from '+@TableName+' where ['+@IDName+']>='+ str(@StartID) +' ORDER BY ['+@IDName+'] 'EXEC(@sql)set nocount offEND
