原文:http://www.kodyaz.com/sql-server-2012/sql-paging-in-sql-server-2012-sql-order-by-offset-fetch-next-rows.aspx

翻译:汪宇杰

SQL Server 2011(译者注:后来重命名为2012)对于T-SQL的特性的增强中,有一个主要的特征就是可以使用“ORDER BY OFFSET n ROWS and FETCH NEXT n ROWS ONLY”语法进行分页。

本文会使用CodePlex上下载的SQL数据库样例AdventureWorks来演示SQL Server 2012 Denali中T-SQL的新特性。

请注意,直到微软把Denali在发布时命名为SQL Server 2012的时候,它在SQL Server社区中本应命名为SQL Server 2011或SQL11。你也可以把SQL Server 2011作为关键词搜索Denali新功能及Transact-sql增强。

在之前版本的SQL Server,比如SQL Server 2005, SQL Server 2008或SQL Server 2008 R2中,T-SQL的ORDER BY语句是被用来在SELECT的结果集中做排序的。

下面的SQL查询演示了SQL Server 2012 CTP1版本前,ORDER BY是怎么弄的:

-- Return all rows from Person table sorted by BusinessEntityID

SELECT BusinessEntityID, FirstName, LastName

FROM Person.Person

ORDER BY BusinessEntityID

返回的结果集包含19972条记录,这是Person表中按主键BusinessEntityID排序的所有记录。T-SQL的SELECT语句中ORDER BY没有其他新玩意儿了。

 

SQL Server 2012中的T-SQL分页:使用ORDER BY OFFSET n ROWS FETCH NEXT n ROWS ONLY

现在,ORDER BY语句中有了OFFSET关键词,这是SQL Server 2012 CTP1中T-SQL的新特性。

如果在ORDER BY中使用OFFSET,结果集将会忽略掉前OFFSET数量条的记录,它不会返回给客户端,但其余的部分仍然会包含在结果集中。

-- Return all rows except the first 10 rows

SELECT BusinessEntityID, FirstName, LastName

FROM Person.Person

ORDER BY BusinessEntityID

  OFFSET 10 ROWS

上面的T-SQL SELECT语句返回了19962跳记录(19972-10)。截图中你可以看到前10条记录没有包含在结果集中。这就是OFFSET 10 ROWS在ORDER BY中是怎样工作的。

 

另一个ORDER BY中的增强是是FETCH NEXT 10 ROWS ONLY语句。这个新特性让SQL程序员可以从SELECT的结果中只获取指定量的数据。比如说,你正在码一个每页10条记录的分页,可以写Fetch Next 10 Rows Only。

-- Return 10 rows after skipping the first 10 rows

SELECT BusinessEntityID, FirstName, LastName

FROM Person.Person

ORDER BY BusinessEntityID

  OFFSET 10 ROWS

  FETCH NEXT 10 ROWS ONLY

请注意,Fetch Next n Rows Only语句只能和Offset n Rows一起在ORDER BY里配合使用。否则的话,SQL Server 2012的SQL引擎会报以下的错误:

Msg 153, Level 15, State 2, Line 5

Invalid usage of the option NEXT in the FETCH statement.

在ORDER BY OFFSET n ROWS FETCH NEXT n ROWS ONLY中使用变量做SQL分页

我们现在使用SQL参数在SQL Server 2012, Denali数据库中创建一个灵活的T-SQL分页语句。

DECLARE @PageNumber int = 6 -- 6th page

DECLARE @RowsCountPerPage int = 10 -- with 10 records per page

-- Returns @RowsCountPerPage rows

-- After skipping the first (@PageNumber-1)*@RowsCountPerPage rows

SELECT BusinessEntityID, FirstName, LastName

FROM Person.Person

ORDER BY BusinessEntityID

OFFSET (@PageNumber-1)*@RowsCountPerPage ROWS

FETCH NEXT @RowsCountPerPage ROWS ONLY

在SQL Server 2012存储过程中使用增强的T-SQL分页

现在是时候在分页的存储过程中使用这个T-SQL增强语法了。你可以从下面的代码里找到个可以被用来作为分页模板的T-SQL分页存储过程。

CREATE PROCEDURE SQL_PAGING_PROCEDURE_SAMPLE

(

  @PageNumber int,

  @RowsCountPerPage int

)

AS

 

SELECT

  BusinessEntityID, FirstName, LastName

FROM Person.Person

ORDER BY BusinessEntityID

  OFFSET (@PageNumber-1)*@RowsCountPerPage ROWS

  FETCH NEXT @RowsCountPerPage ROWS ONLY

GO

下面的输出显示了在SQL Server 2012中实现分页是多么的简单,Denali 数据库配合ORDER BY OFFSET n ROWS and FETCH NEXT n ROWS ONLY语句。