SQL Server 2012 使用OFFSET FETCH轻松实现分页

SQL Server 2012中提供了新的T-SQL（OFFSETFETCH）可以让我们更方便的实现分页功能。

 

先看一下以前是如何实现分页的:

 

----使用ROW_NUMBER() CTE查找第一页20行记录

WITH Paging AS

(

SELECT

       TransactionID

       , ProductID

       , TransactionDate

       , Quantity

       , ActualCost

       , ROW_NUMBER()OVER (ORDERBY TransactionDateDESC) AS RowNumber

FROM

       Production.TransactionHistory

)

SELECT

       TransactionID

       , ProductID

       , TransactionDate

       , Quantity

       , ActualCost

FROM

       Paging

WHERE RowNumber> 0 AND RowNumber <= 20

 

 

------使用 OFFSET FETCH查询前20行记录

 

SELECT

       TransactionID

       , ProductID

       , TransactionDate

       , Quantity

       , ActualCost

FROM

       Production.TransactionHistory

ORDER BY TransactionDate DESC

OFFSET 0 ROWS

FETCH NEXT 20 ROWS ONLY;

 

 

使用OFFSET FETCH简便吧。 由于OFFSETFETCH可以使用变量，所以很容易实现分页。

 

--返回第三页数据

DECLARE @Page tinyint = 2

      , @FetchRowstinyint = 20;

SELECT

       TransactionID

       , ProductID

       , TransactionDate

       , Quantity

       , ActualCost

FROM

       Production.TransactionHistory

ORDER BY TransactionDate DESC

OFFSET @Page*20+1ROWS

FETCH NEXT @FetchRows ROWS ONLY;

 

 

两种写法其实性能上没有太大的改变，但是借助OFFSET FETCH开发变的非常容易。

 

注意事项：使用 OFFSET和 FETCH 限制返回的行数

---

建议您使用 OFFSET 和 FETCH 子句而不是 TOP子句实现查询分页解决方案，并限制发送到客户端应用程序的行数。

如果将 OFFSET 和 FETCH作为分页解决方案，则需要为返回到客户端应用程序的每“页”数据运行一次查询。例如，要以 10 行为增量返回查询结果，您必须执行一次查询以返回 1-10行，然后再次运行查询以返回 11- 20行，依此类推。每个查询都是独立的，不会以任何方式与其他查询相关联。这意味着，与使用执行一次查询并在服务器上保持状态的游标不同，将由客户端应用程序负责跟踪状态。若要使用 OFFSET和 FETCH 在查询请求之间获得稳定的结果，必须满足以下条件：

1.      查询使用的基础数据不能发生变化。即，不会更新查询处理的行，也不会在单个事务中使用快照或可序列化事务隔离执行查询中的所有页面请求。有关这些事务隔离级别的详细信息，请参阅SET TRANSACTIONISOLATION LEVEL (Transact-SQL)。

2.      ORDER BY 子句包含保证是唯一的列或列组合。

 

 

更多内容参考：http://msdn.microsoft.com/zh-cn/library/ms188385(v=sql.110).aspx