IT工程師數位筆記本

SQL Server SQL分頁查詢的幾種方式

目錄

0.    序言    

1.    TOP…NOT IN…    

2.    ROW_NUMBER()    

3.    OFFSET…FETCH    

4.    執行計劃    

5.    補充   

1. 0.序言

總結一下SQL Server種常用的幾種分頁查詢：

    本示例中用的時已有的表，建表不規范，Name作為主鍵，建議實際使用中專門設置主鍵并且WHERE條件中盡可能使用主鍵。

參數說明：

@pageSize:分頁查詢每頁N條數據時每頁期望的數據量N

    @offset:分頁查詢第I頁每頁N條數據時，第I頁之前的N*（I-1）條數據

舉個栗子：假如我們要查詢第3頁的數據，每頁10條數據，則 @pageSize為10，@offset為20。

基本原理：查詢 @pageSize 條數據，先使用一個子查詢查詢出符合查詢條件的 @offset條數據的主鍵，再使用TOP @pageSize查詢@pageSize條數據，并且再WHERE從句中使用 NOT IN 關鍵詞來對數據進行篩選。

基本原理：在SQL Server2005之后加入，可以使用 ROW_NUMBER()函數為查詢出來的記錄生成一個行號，需要指定一個ORDER BY 子句確定排序方式，排序方式不同，行號也可能不同。詳細說明:ROW_NUMBER()

本文只涉及OVER從句中跟隨ORDER BY子句，partition by 從句不在本文討論范圍內，partition by 和OVER詳細說明戳這里

這里使用了兩個ROW_NUMBER()函數的例子，這兩個計算總行數的方式是不一樣的，本文結尾處會對比一個兩種方式的IO操作以說明哪種方式更適合

OFFSET是SQL Server 2012中新增的語法，可以單獨使用，也可與FETCH NEXT一起使用，單獨使用OFFSET時是查詢獲取@offset之后所有的數據,如下圖所示

但我們想要的是分頁查詢，那就需要和FETCH NEXT聯合使用，OFFSET后跟@offset參數，FETCH NEXT 后跟 @pageSize參數

 　　4.執行計劃

上面四種查詢方式的執行計劃如下：

OFFSET…FETCH補充：

關于參數，推薦用法：始終使用ROWS，始終使用NEXT

-- OFFSET {@offset} ROWS FETCH NEXT {@pagesize} ROWS ONLY

/*

*使用 OFFSET-FETCH 中的限制：

    *** ORDER BY 是使用 OFFSET 和 FETCH 子句所必需的。

    *** OFFSET 子句必須與 FETCH 一起使用。永遠不能使用 ORDER BY … FETCH。

    *** TOP 不能在同一個查詢表達式中與 OFFSET 和 FETCH 一起使用。

    *** OFFSET/FETCH 行計數表達式可以是將返回整數值的任何算術、常量或參數表達式。該行計數表達式不支持標量子查詢。

*/

更多OFFSET信息參考這里

對比一下ROW_NUMBER()兩種計算數據總數方式的IO消耗：

第一個是使用MAX(RowNum)來計算總數的，第二種是使用子查詢的方式來計算總數。

示例SQL：PagedQuery