SQL Server分頁查詢幾種方式介紹 0. 序言 1. TOP…NOT IN… 2. ROW_NUMBER() 3. OFFSET…FETCH 4. 執行計劃 5. 補充 ...
SQL Server SQL分頁查詢的幾種方式
目錄
總結一下SQL Server種常用的幾種分頁查詢：
本示例中用的時已有的表，建表不規範，Name作為主鍵，建議實際使用中專門設置主鍵並且WHERE條件中儘可能使用主鍵。
參數說明：
@pageSize:分頁查詢每頁N條數據時每頁期望的數據量N
@offset:分頁查詢第I頁每頁N條數據時，第I頁之前的N*（I-1）條數據
舉個慄子：假如我們要查詢第3頁的數據，每頁10條數據，則 @pageSize為10，@offset為20。
1.TOP…NOT IN…
基本原理：查詢 @pageSize 條數據，先使用一個子查詢查詢出符合查詢條件的 @offset條數據的主鍵，再使用TOP @pageSize查詢@pageSize條數據，並且再WHERE從句中使用 NOT IN 關鍵詞來對數據進行篩選。
2.ROW_NUMBER()
基本原理：在SQL Server2005之後加入，可以使用 ROW_NUMBER()函數為查詢出來的記錄生成一個行號，需要指定一個ORDER BY 子句確定排序方式，排序方式不同，行號也可能不同。詳細說明:ROW_NUMBER()
本文只涉及OVER從句中跟隨ORDER BY子句，partition by 從句不在本文討論範圍內，partition by 和OVER詳細說明戳這裡
這裡使用了兩個ROW_NUMBER()函數的例子，這兩個計算總行數的方式是不一樣的，本文結尾處會對比一個兩種方式的IO操作以說明哪種方式更適合
3.OFFSET…FETCH
OFFSET是SQL Server 2012中新增的語法，可以單獨使用，也可與FETCH NEXT一起使用，單獨使用OFFSET時是查詢獲取@offset之後所有的數據,如下圖所示
但我們想要的是分頁查詢，那就需要和FETCH NEXT聯合使用，OFFSET後跟@offset參數，FETCH NEXT 後跟 @pageSize參數
上面四種查詢方式的執行計劃如下：
5.補充
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
