博客

分页实现的方法又多了一种，在SQL Server 2012版本中，TSQL在Order By子句中新增 Offset-Fetch子句，用于从有序的结果集中，跳过一定数量的数据行，获取指定数量的数据行，从而达到数据行分页的目的。经过测试，从逻辑读取数量和响应的时间消耗来测评，使用Offset-Fetch实现的分页方式，比Row_Number()方式性能要高很多。

Offset-Fetch子句要求结果集是有序的，因此，只能用于order by 子句中，语法如下：

ORDER BY order_by_expression [ ASC | DESC ]  [ ,...n ] [ <offset_fetch> ]
<offset_fetch> ::=
{ 
    OFFSET { integer_constant | offset_row_count_expression } ROWS
    [ FETCH NEXT {integer_constant | fetch_row_count_expression } ROWS ONLY ]
}

关键字解析：

• 
  
  Offset子句
  
  ：用于指定跳过（Skip）的数据行；
  
• 
  
  Fetch子句
  
  ：该子句在Offset子句之后执行，表示在跳过（Sikp）指定数量的数据行之后，返回一定数据量的数据行；
  
• 
  
  执行顺序
  
  ：Offset子句必须在Order By 子句之后执行，Fetch子句必须在Offset子句之后执行；
  

分页实现的思路：

1. 
   
   在分页实现中，使用Order By子句，按照指定的columns对结果集进行排序；
   
   
2. 
   
   使用Offset子句跳过前N页：Offset (@PageIndex-1)*@RowsPerPage rows；
   
   
3. 
   
   使用Fetch子句呈现当前Page：Fetch next @RowsPerPage rows only；
   
   

一，Offset-Fetch 实现分页的示例代码

1，创建示例数据

View Code

2，使用Offset子句跳过指定数目的数据行

select * 
from dbo.dt_test
order by id
offset 2 ROWS

3，使用Offset-Fetch子句跳过指定数目的数据行之后，返回指定数目的数据行

select * 
from dbo.dt_test
order by id
offset 2 ROWS
FETCH Next 2 rows only

4，修改成分页的通用格式

--分页的索引，页码从0开始
Declare @PageIndex int
--每页显示的行数
Declare @Size int

set @PageIndex=1
set @Size=2

select * 
from dbo.dt_test
order by id
OFFSET @PageIndex*@Size ROWS
FETCH next @Size rows only

二，排序（order by）

order by子句的语法是：ORDER BY order_by_expression ，用于按照指定字段进行排序，通常有3种写法：

• 
  select子句中列的name，或alias，排序子句（order by）的执行顺序在select子句之后，可以使用列的Alias进行排序；
  
• 
  表达式，按照表达式的计算结果进行排序；
  
• 
  select子句中列的序号，从1开始，此处的数值是序号，不建议使用；
  

上述三种写法都会对查询结果集进行排序，返回的结果集是有序的，但是，如果这样写，在order by子句中使用一个常量：

order by (select 1) 

该子句中的 1 不是列的序号，而是常量，SQL Server按照结果集的原始顺序返回，order by子句不对结果集排序。

参考文章：

ORDER BY Clause (Transact-SQL)

SQL Server 2012使用OFFSET/FETCH NEXT分页及性能测试

SQL Server 2012提供的OFFSET/FETCH NEXT与Row_Number()对比测试