SQL Server 多種常見分頁語法，及最新分頁語法 OFFSET 跟 FETCH

在SQL Server 2012以前SQL Server 2000、SQL Server 2005、SQL Server 2008、SQL Server 2008 R2等版本，要實現分頁結果，通常使用以下三種方式：

表中主鍵必須為標識列，[ID] int IDENTITY (1,1)
第一種分頁SQL語法與說明：(利用Not In和SELECT TOP分頁)
SQL語法：
SELECT TOP 10 *
    FROM [Northwind].[dbo].[Orders]
    WHERE (ID NOT IN
        (SELECT TOP 20 id
            FROM [Northwind].[dbo].[Orders]
            ORDER BY id
        )
    )
    ORDER BY ID

SQL語法說明：
SELECT TOP 每頁希望顯示的筆數 *
    FROM 查詢資料表
    WHERE (ID NOT IN
        (SELECT TOP 每頁希望顯示的筆數*頁數 id
            FROM 查詢資料表
            ORDER BY id
        )
    )
    ORDER BY ID


第二種分頁語法與說明：(利用ID大於多少和SELECT TOP分頁）
SQL語法：
SELECT TOP 10 *
    FROM [Northwind].[dbo].[Orders]
    WHERE (ID >
        (SELECT MAX(id)
            FROM (SELECT TOP 20 id
                FROM [Northwind].[dbo].[Orders]
                ORDER BY id) AS T
            )
        )
        ORDER BY ID

SQL語法說明：
SELECT TOP 每頁希望顯示的筆數 *
    FROM 查詢資料表
    WHERE (ID >
        (SELECT MAX(id)
            FROM (SELECT TOP 每頁希望顯示的筆數*頁數 id
                FROM 查詢資料表
                ORDER BY id) AS T
            )

        )
        ORDER BY ID


第三種分頁語法與說明：(利用SQL的遊標存儲過程分頁)

create  procedure SqlPager
@sqlstr nvarchar(4000), --查詢字符串
@currentpage int, --第N頁
@pagesize int --每頁行數
as
set nocount on
declare @P1 int, --P1是遊標的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 總頁數--,@rowcount as 總行數,@currentpage as 當前頁
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off

第一種和第二種分頁語法，如果沒有主鍵，可以使用臨時資料表，另外也可以使用第三種分頁語法執行，但是執行效能會比較低。
為了資料庫效能考量，以第一種、第二種為主，並加上主鍵和索引，另外以最優化為優先時，當然又以第二種作為查詢效能會最高。

透過SQL 查詢效能分析器，顯示比較：我的結論是:
第二種分頁語法：(利用ID大於多少和SELECT TOP分頁）效能最高，需要拼接SQL語句
第一種分頁語法：(利用Not In和SELECT TOP分頁)   效能次之，需要拼接SQL語句
第三種分頁語法：(利用SQL的遊標存儲過程分頁)    效能最差，但是最為通用

 

另外，也有一些程式設計師為了要達到分頁的功能，

會將查詢到的結果集(所有筆數)，傳到程式端處理，

在單機可能可以不用考慮相關效能問題，

但如果是應用在行動裝置的 APP 上，那可能就會有致命的問題了，

例如查詢結果集總共用有10,000筆，這樣在每次換頁時，就都須要查詢、傳送10,000筆，

在APP程式中再去處理出指定的頁數筆數，

光是想像，就知道在傳輸速度以及處理效能上，會有非常大的問題。

從 SQL Server 2005 一直到 SQL Server 20008R2，如果想要在資料庫中，取出已分頁的資料筆數，會使用 ROW_NUMBER() 這個分頁語法來處理。

語法如下：

SELECT * From(
    SELECT ROW_NUMBER() OVER(ORDER BY OrderID DESC) num,*
    FROM [Northwind].[dbo].[Orders]
) a
WHERE num Between 101 And 150

透過以上的語法查詢 Orders 訂單資料表，並且使用 OrderID 訂單號碼由大至小排序，查詢第 101 到 150 共 50 筆的訂單資料。

 

到了 SQL Server 2012 微軟提供了一個新的專為分頁而設計的語法，主要是擴充了 Order By 的子句 OFFSET ROWS 跟 FETCH NEXT ROWS。

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

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

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

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

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

 

下面語法依據salesorderid欄位來排序，

SELECT * FROM [Northwind].[dbo].[Orders]
    ORDER BY OrderID
    OFFSET 100 ROWS
    FETCH NEXT 50 ROWS ONLY

上述的語法，說明如下。

主要以 OrderId 欄位作為排序的依據。

直接「跳過 100 筆資料」(OFFSET 100 ROWS)。

從「第 101 筆資料開始取得接下來的 50 筆」(FETCH NEXT 50 ROWS ONLY)

取得區段的訂單資料，也就依據這個特性來達到分頁的結果。

 

透過 SQL Server Profiler 偵測可以比較確定，透過 SQL Server 2012 所提供的新語法，使用的資源較少、速度較快，因此，強烈建議如果你SQL Server已經是 2012 以上的版本，就盡快改用新的分頁語法，來提高程式查詢分頁的效能吧。

 
  • Top