SQL Server2012 T-SQL對分頁的增強嘗試

簡介

    SQL Server 2012中在Order By子句之後新增了OFFSET和FETCH子句來限制輸出的行數進而達到了分頁效果。相比較SQL Server 2005/2008的ROW_Number函數而言，使用OFFSET和FETCH不僅僅是從文法角度更加簡單，并且擁有了更優的性能(看到很多人下過這個結論，但我測試有所偏差，暫且保留意見)。

    MSDN上對于OFFSET和FETCH的較長的描述可以在（http://msdn.microsoft.com/en-us/library/ms188385%28v=SQL.110%29.aspx）找到。

OFFSET和FETCH

    這兩個關鍵字在MSDN原型使用方式如代碼1所示。

ORDER BY order_by_expression

    [ COLLATE collation_name ] 

    [ ASC | DESC ] 

    [ ,...n ] 

[ <offset_fetch> ]

<offset_fetch> ::=

{ 

    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }

    [

      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY

    ]

}

    代碼1.OFFSET和FETCH在MSDN的原型

    可以看到，OFFSET使用起來很簡單，首先在OFFSET之後指定從哪條記錄開始取。其中，取值的數可以是常量也可以是變量或者表達式。而Row和ROWS在這裡是一個意思。

    然後通過FETCH關鍵字指定取多少條記錄。其中，FIRST和NEXT是同義詞，和前面的ROW和ROWS一樣，它們可以互相替換。同樣，這裡取的記錄條數也可以是常量或者變量表達式。

    下面通過一個例子來看OFFSET和FETCH的簡單用法。首先建立測試資料，這裡我就偷懶了，使用我上篇文章的測試資料，建立表後插入100萬條測試資料，這個表非常簡單，一個自增的id字段和一個int類型的data字段，建立表的語句我就不貼了，插入測試資料的代碼如圖1所示。

    4

     圖1.插入測試資料

    下面，我要取第500000到500100的資料，如圖2所示。

    1

    圖2.取50萬到500100之間的資料

     可以看到，使用OFFSET和FETCH關鍵字使分頁變得如此簡單。

OFFSET…FETCH分頁對性能的提升

    OFFSET和FETCH語句不僅僅是文法糖，還能帶來分頁效率上的提升。下面我們通過一個例子進行比較SQL Server 2012和SQL Server 2005/2008不同分頁方式的分頁效率。我們同樣取50萬到500100之間的資料，性能對比如圖3所示。

    2

     圖3.SQL Server 2012分頁和SQL Server 05/08之間分頁效率對比

     但是，查詢計劃中我看到SQL Server2012中FETCH..NEXT卻十分損耗性能。這和前面的測試結果嚴重不符，如圖4所示。

    3

    圖4.兩種方式的執行計劃

    通過對比掃描聚集索引這步，我發現對于估計執行行數存在嚴重偏差,如圖5所示。

    45

    圖5.存在偏差的執行計劃

    上圖中，第一張圖檔是使用OFFSET…FETCH進行分頁的。估計行數居然占到了500100，嚴重不符。這令我十分費解，暫時還沒有找出原因，求各路大神指導….

總結

    SQL Server 2012帶來的分頁效果十分強大，使得大大簡化在SQL Server下的分頁。對于性能的影響，由于出現了上述執行計劃的偏差，暫且不下結論。待日後研究有了進展再來補上。

本文轉自CareySon部落格園部落格，原文連結：http://www.cnblogs.com/CareySon/archive/2012/03/09/2387825.html，如需轉載請自行聯系原作者