你的位置:首页 > 数据库

[数据库]SQL Server SQL分页查询的几种方式介绍


SQL Server SQL分页查询的几种方式

目录

0.    序言    

1.    TOP…NOT IN…    

2.    ROW_NUMBER()    

3.    OFFSET…FETCH    

4.    执行计划    

5.    补充   

 

 

 

  1. 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