SQLSERVER2012的分页新功能

SQLSERVER2012的分页新功能

 简介

   SQL Server 2012中在Order By子句之后新增了OFFSET和FETCH子句来限制输出的行数从而达到了分页效果。相比较SQL Server 2005/2008的ROW_Number函数而言，使用OFFSET和FETCH不仅仅是从语法角度更加简单，并且拥有了更优的性能。

限制返回的行数

以下示例使用 OFFSET 和 FETCH 限制查询返回的行数。

A.指定整数常量以提供 OFFSET 和 FETCH 值

以下示例将一个整数常量指定为 OFFSET 和 FETCH 子句的值。

第一个查询返回所有按 DepartmentID 列排序的行。

将此查询返回的结果与后面的两个查询的结果进行比较。

下一个查询使用 OFFSET 5 ROWS 子句跳过前 5 行，然后返回所有其余行。

最终查询使用 OFFSET 0 ROWS 子句从第一行开始，然后使用 FETCH NEXT 10 ROWS ONLY 将返回的行限制为排序的结果集中的 10 行。

  USE AdventureWorks2012;

  GO

  -- Return all rows sorted by the column DepartmentID.

  SELECT DepartmentID, Name, GroupName

  FROM HumanResources.Department

  ORDER BY DepartmentID;

  -- Skip the first 5 rows from the sorted result set and return all remaining rows.

  SELECT DepartmentID, Name, GroupName

  FROM HumanResources.Department

  ORDER BY DepartmentID OFFSET 5 ROWS;

  -- Skip 0 rows and return only the first 10 rows from the sorted result set.

  SELECT DepartmentID, Name, GroupName

  FROM HumanResources.Department

  ORDER BY DepartmentID

      OFFSET 0 ROWS

      FETCH NEXT 10 ROWS ONLY;

B.指定变量以提供 OFFSET 和 FETCH 值

以下示例声明 @StartingRowNumber 和 @FetchRows 变量，并在 OFFSET 和 FETCH 子句中指定这些变量。

USE AdventureWorks2012;

  GO

  -- Specifying variables for OFFSET and FETCH values

  DECLARE @StartingRowNumber tinyint = 1

        , @FetchRows tinyint = 8;

  SELECT DepartmentID, Name, GroupName

  FROM HumanResources.Department

  ORDER BY DepartmentID ASC

      OFFSET @StartingRowNumber ROWS

      FETCH NEXT @FetchRows ROWS ONLY;

 C.指定表达式以提供 OFFSET 和 FETCH 值

以下示例使用 @StartingRowNumber - 1 表达式指定 OFFSET 值，并使用 @EndingRowNumber - @StartingRowNumber + 1 表达式指定 FETCH 值。

另外，还指定了查询提示 OPTIMIZE FOR。

在编译和优化查询时，可以使用此提示为局部变量提供特定的值。

仅在查询优化期间使用该值，在查询执行期间不使用该值。

USE AdventureWorks2012;

  GO

  -- Specifying expressions for OFFSET and FETCH values

  DECLARE @StartingRowNumber tinyint = 1

        , @EndingRowNumber tinyint = 8;

  SELECT DepartmentID, Name, GroupName

  FROM HumanResources.Department

  ORDER BY DepartmentID ASC

      OFFSET @StartingRowNumber - 1 ROWS

      FETCH NEXT @EndingRowNumber - @StartingRowNumber + 1 ROWS ONLY

  OPTION ( OPTIMIZE FOR (@StartingRowNumber = 1, @EndingRowNumber = 20) );

D.指定常数标量子查询以提供 OFFSET 和 FETCH 值

以下示例使用常数标量子查询定义 FETCH 子句的值。

该子查询从 dbo.AppSettings 表的 PageSize 列中返回单个值。

-- Specifying a constant scalar subquery

  USE AdventureWorks2012;

  GO

  CREATE TABLE dbo.AppSettings (AppSettingID int NOT NULL, PageSize int NOT NULL);

  GO

  INSERT INTO dbo.AppSettings VALUES(1, 10);

  GO

  DECLARE @StartingRowNumber tinyint = 1;

  SELECT DepartmentID, Name, GroupName

  FROM HumanResources.Department

  ORDER BY DepartmentID ASC

      OFFSET @StartingRowNumber ROWS

      FETCH NEXT (SELECT PageSize FROM dbo.AppSettings WHERE AppSettingID = 1) ROWS ONLY;

详细的官方资料请参阅 http://msdn.microsoft.com/en-us/library/ms188385%28v=SQL.110%29.aspx