SQL Server SQL分页查询

搜鞥都哦

　　SQL Server SQL分页查询的几种方式
　　目录
　　0.    序言   
　　1.    TOP…NOT IN…   
　　2.    ROW_NUMBER()   
　　3.    OFFSET…FETCH   
　　4.    执行计划   
　　5.    补充   

• 
  0.序言
  

　　总结一下SQL Server种常用的几种分页查询：
　　    本示例中用的时已有的表，建表不规范，Name作为主键，建议实际使用中专门设置主键并且WHERE条件中尽可能使用主键。
　　参数说明：
　　@pageSize:分页查询每页N条数据时每页期望的数据量N
　　    @offset:分页查询第I页每页N条数据时，第I页之前的N*（I-1）条数据
　　举个栗子：假如我们要查询第3页的数据，每页10条数据，则 @pageSize为10，@offset为20。

1.TOP…NOT IN… 　　基本原理：查询 @pageSize 条数据，先使用一个子查询查询出符合查询条件的 @offset条数据的主键，再使用TOP @pageSize查询@pageSize条数据，并且再WHERE从句中使用 NOT IN 关键词来对数据进行筛选。

2.ROW_NUMBER() 　　基本原理：在SQL Server2005之后加入，可以使用 ROW_NUMBER()函数为查询出来的记录生成一个行号，需要指定一个ORDER BY 子句确定排序方式，排序方式不同，行号也可能不同。详细说明:ROW_NUMBER()

　　本文只涉及OVER从句中跟随ORDER BY子句，partition by 从句不在本文讨论范围内，partition by 和OVER详细说明戳这里

　　这里使用了两个ROW_NUMBER()函数的例子，这两个计算总行数的方式是不一样的，本文结尾处会对比一个两种方式的IO操作以说明哪种方式更适合

3.OFFSET…FETCH 　　OFFSET是SQL Server 2012中新增的语法，可以单独使用，也可与FETCH NEXT一起使用，单独使用OFFSET时是查询获取@offset之后所有的数据,如下图所示

　　但我们想要的是分页查询，那就需要和FETCH NEXT联合使用，OFFSET后跟@offset参数，FETCH NEXT 后跟 @pageSize参数

　　4.执行计划
　　上面四种查询方式的执行计划如下：

5.补充 　　OFFSET…FETCH补充：

　　关于参数，推荐用法：始终使用ROWS，始终使用NEXT
　　-- OFFSET {@offset} ROWS FETCH NEXT {@pagesize} ROWS ONLY
　　/*
　　*使用 OFFSET-FETCH 中的限制：
　　    *** ORDER BY 是使用 OFFSET 和 FETCH 子句所必需的。
　　    *** OFFSET 子句必须与 FETCH 一起使用。永远不能使用 ORDER BY … FETCH。
　　    *** TOP 不能在同一个查询表达式中与 OFFSET 和 FETCH 一起使用。
　　    *** OFFSET/FETCH 行计数表达式可以是将返回整数值的任何算术、常量或参数表达式。该行计数表达式不支持标量子查询。
　　*/
　　更多OFFSET信息参考这里
　　对比一下ROW_NUMBER()两种计算数据总数方式的IO消耗：

　　第一个是使用MAX(RowNum)来计算总数的，第二种是使用子查询的方式来计算总数。
　　示例SQL：PagedQuery