SQL Server 2012 ：分頁處理：認識 OFFSET 和 FETCH 子句

適用版本：SQL Server 2012。

OFFSET - FETCH 是 ORDER BY 子句的延伸功能。

使用 OFFSET 和 FETCH 限制傳回的資料列。
讓你可以過濾篩選特定範圍的資料列。

提供了對結果集的分頁處理功能。
可以指定跳過的行數，指定要取回的資料列筆數。

而且，OFFSET 和 FETCH 子句是依據 draft ANSI SQL:2011 標準。
因此，會比 TOP 子句具備更好的 SQL 語言相容性。

語法：

ORDER BY {order_by_list}
OFFSET {offset_value} ROW(S)
FETCH FIRST|NEXT {fetch_value} ROW(S) ONLY

其中 OFFSET 是必要子句，不可以省略，但 OFFSET 是選擇性子句。

---

請參考以下的範例程式碼： 使用 OFFSET 和 FETCH 來限制查詢所傳回的資料列數目

EX1. 為 OFFSET 和 FETCH 值指定整數常數

-- 查詢資料表的內容
USE Northwind
GO
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders;
GO

-- 00_查詢資料表的內容

-- 使用 TOP 子句：查詢最近的 50 筆訂單記錄，0.042186
SELECT TOP 50 OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
ORDER BY OrderDate DESC;
GO

-- 01_使用 TOP 子句：查詢最近的 50 筆訂單記錄

--  使用 OFFSET 和 FETCH 子句：查詢最近的 50 筆訂單記錄，0.042186
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
ORDER BY OrderDate DESC
 OFFSET 0 ROWS 
 FETCH FIRST 50 ROWS ONLY;
GO

-- 02_使用 OFFSET 和 FETCH 子句：查詢最近的 50 筆訂單記錄

--  使用 OFFSET 和 FETCH 子句：依據日期排序，但是跳過前 10 筆資料列
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
ORDER BY OrderDate DESC
 OFFSET 10 ROWS;
GO

-- 03_使用 OFFSET 和 FETCH 子句：依據日期排序，但是跳過前 10 筆資料列

---

EX2. 為 OFFSET 和 FETCH 值指定變數

-- 使用 OFFSET 和 FETCH
-- 依據日期排序，但是跳過前 10 筆資料列，再取 10 筆資料列；也就是說：依據日期排序，但僅查詢第 11 到 20 筆的資料列。
-- 0.042196
USE Northwind
GO
DECLARE @OFFSET tinyint =10, @FETCH tinyint =10

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
ORDER BY OrderDate DESC
 OFFSET @OFFSET ROWS
 FETCH NEXT @FETCH ROWS ONLY;
GO

-- 04_使用 OFFSET 和 FETCH 子句：依據日期排序，但僅查詢第 11 到 20 筆的資料列

-- 05_檢視回傳的資料列

-- 使用 ROW_NUMBER() 函數
-- 傳回結果集資料分割內某資料列的序號，序號從 1 開始，每個資料分割第一個資料列的序號是 1。
-- 適用版本：SQL Server 2005、2008、2008 R2、2012
-- 0.042294
DECLARE @begin tinyint =11, @end tinyint =20

SELECT OrderID, CustomerID, EmployeeID, OrderDate 
FROM(
 SELECT ROW_NUMBER() OVER (ORDER BY OrderDate DESC) rid, 
  OrderID, CustomerID, EmployeeID, OrderDate
 FROM dbo.Orders) rktb
WHERE rid BETWEEN @begin AND @end
GO

-- 06_使用 ROW_NUMBER() 函數：依據日期排序，但僅查詢第 11 到 20 筆的資料列

與 OFFSET 和 FETCH 子句比較起來，使用 ROW_NUMBER() 函數會多耗用一點資源。

---

EX3. 為 OFFSET 和 FETCH 值指定常數純量子查詢

-- 依據日期排序，跳過前 10 筆資料列，利用亂數 RAND() 函數，隨機查詢前 0 ~ 99 筆資料列。
USE Northwind
GO
DECLARE @OFFSET tinyint =10

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
ORDER BY OrderDate DESC
 OFFSET @OFFSET ROWS
 FETCH NEXT (SELECT CAST(RAND()*100 AS tinyint)) ROWS ONLY;
GO

-- 07_為 OFFSET 和 FETCH 值指定常數純量子查詢

---

認識 OFFSET 和 FETCH 子句

語法如下：

ORDER BY order_by_expression
    [ COLLATE collation_name ] 
    [ ASC | DESC ] 
    [ ,...n ] 
[  ]

 ::=
{ 
    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
    [
      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
    ]
}

(1)
OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }

指定要略過的資料列數目，然後才開始從查詢運算式傳回資料列。
值可以是大於或等於零的整數常數或運算式。

offset_row_count_expression 可以是變數、參數或常數純量子查詢。
在使用子查詢時，它無法參考定義在外部查詢範圍中的任何資料行。也就是，它不能與外部查詢相互關聯。

ROW 和 ROWS 是同義字，基於 ANSI 相容性提供它們。

在查詢執行計畫中，位移資料列計數值會顯示在 TOP 查詢運算子的 Offset 屬性中。

(2)
FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY

指定要在已處理 OFFSET 子句之後傳回的資料列數目。
值可以是大於或等於一的整數常數或運算式。

fetch_row_count_expression 可以是變數、參數或常數純量子查詢。
在使用子查詢時，它無法參考定義在外部查詢範圍中的任何資料行。 也就是，它不能與外部查詢相互關聯。

FIRST 和 NEXT 是同義字，基於 ANSI 相容性提供它們。
ROW 和 ROWS 是同義字，基於 ANSI 相容性提供它們。

在查詢執行計畫中，位移資料列計數值會顯示在 TOP 查詢運算子的 Rows 或 Top 屬性中。

---

(一)
在允許 TOP 和 ORDER BY 的任何查詢中，都可以使用 OFFSET 和 FETCH，但有下列限制：

3-1：OVER 子句不支援 OFFSET 和 FETCH。

3-2：OFFSET 和 FETCH 不能直接在 INSERT、UPDATE、MERGE 和 DELETE 陳述式中指定，但是可以在這些陳述式中所定義的子查詢中指定。
例如，在 INSERT INTO SELECT 陳述式中，OFFSET 和 FETCH 可以在 SELECT 陳述式中指定。

3-3：在使用 UNION、EXCEPT 或 INTERSECT 運算子的查詢中，只能在指定查詢結果順序的最後查詢中指定 OFFSET 和 FETCH。

3-4：TOP 無法與相同查詢運算式 (相同查詢範圍) 中的 OFFSET 和 FETCH 結合。

(二)
使用 OFFSET 和 FETCH 限制傳回的資料列

我們建議您使用 OFFSET 和 FETCH 子句 (而不要使用 TOP 子句)，來實作查詢分頁方案，並限制傳送給用戶端應用程式的資料列數目。

如果使用 OFFSET 和 FETCH 做為分頁方案，需要針對傳回給用戶端應用程式的每一「頁」資料執行查詢一次。

例如，若要以 10 個資料列的增量傳回查詢結果，您必須執行一次查詢傳回 1 到 10 的資料列，然後再執行一次查詢傳回 11 到 20 的資料列，依此類推。
每個查詢各自獨立，無論如何都不相關。
這表示，不同於使用資料指標執行查詢一次，並在伺服器上維護狀態，用戶端應用程式會負責追蹤狀態。

若要使用 OFFSET 和 FETCH，在查詢要求之間達到穩定結果，必須符合下列條件：

(1) 查詢所使用的基礎資料不可以變更。

也就是說，查詢接觸的資料列不會更新，或者對網頁的所有查詢要求都是在使用快照集或可序列化交易隔離的單一交易中執行。
如需這些交易隔離等級的詳細資訊，請參閱＜SET TRANSACTION ISOLATION LEVEL (Transact-SQL)＞。

ORDER BY 子句包含的資料行或資料行組合保證是唯一。

請參閱本主題稍後的＜範例＞一節中的範例＜在單一交易中執行多個查詢＞。

(2) 如果一致的執行計畫是分頁方案的要素，請考慮為 OFFSET 和 FETCH 參數使用 OPTIMIZE FOR 查詢提示。

如需有關 OPTIMZE FOR 的詳細資訊，請參閱＜查詢提示 (Transact-SQL)＞。

---

參考資料

ORDER BY 子句 (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms188385%28v=SQL.110%29.aspx

TOP (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms189463.aspx

次序函數 (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms189798.aspx

ROW_NUMBER (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms186734.aspx

SQL:2011
http://en.wikipedia.org/wiki/SQL:2011

What's new in SQL:2011
http://www.sigmod.org/publications/sigmod-record/1203/pdfs/10.industry.zemke.pdf

Implement LIMIT keyword
http://connect.microsoft.com/SQLServer/feedback/details/124495/implement-limit-keyword