T-SQL筆記15_三種分頁查詢寫法_避免Server壓力過大Loading等待太久(支援自SQL2000,2005,2012後)

- 4月 08, 2021

在網頁中通常做表格資料的呈現最怕就是資料量大到幾千筆甚至到萬筆查詢Loading過久，Server負荷不來的情況。

此時在網頁上通常會做一些分頁減輕查詢耗費資源與時間成本

(每頁一次查詢只先呈現10~20、30幾筆之類的分頁小批量查詢機制)

而不一次就要將上萬筆資料先全查出來，等loading轉圈圈1分鐘甚至到5~8分鐘過後網頁資料才出現。

有了上述的概念後接著就記錄一下常用到的透過SQL分頁查詢語法段落

這裡的目標結果TABLE可能會是一個join起來的table集合也可能是單一一個temp table

看實際應用情境

以我的情境是join的情況

我要套分頁機制的目標結果Table

是來自於這段SQL

這裡pat_patNo為專利的流水編號

寫法1.用最硬解的SELECT TOP方式，可支援SQL 2000的環境

DECLARE @PageIndex INT = 1, @PageSize INT = 25
SELECT TOP @PageSize *
FROM
{目標結果Table}
WHERE
(   {具唯一跟可排序性欄位} NOT IN 
	(SELECT TOP (@PageSize*(@PageIndex-1)) {具唯一跟可排序性欄位} 
	 FROM {目標結果Table} 
	 ORDER BY {具唯一跟可排序性欄位} ASC/DESC)) //skip(@PageSize*(@PageIndex-1)) 條記錄
ORDER BY {具唯一跟可排序性欄位} ASC/DESC

寫法2.透過ROW_NUMBER()搭配OVER(ORDER BY 特定某個具唯一性跟可排序性的Field)跟BETWEEN來實踐

備註:ROW_NUMBER()是自SQL Server2005開始就適用

具體寫法模式

DECLARE @PageIndex INT = 1, @PageSize INT = 25

SELECT * FROM
(
    SELECT
        *, ROW_NUMBER() OVER (ORDER BY {具唯一跟可排序性欄位} ASC/DESC) as RowId
    FROM
        {目標結果Table}
    WHERE
        Conditions
) AS vw
WHERE
    RowId BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize

我們可以藉由設定每頁要呈現多少筆資料的PageSize跟第幾頁的頁碼達到一個分頁Shift的功能

寫法3.藉由OFFSET搭配FETCH

備註:OFFSET跟FETCH是自SQL Server 2012後才有的機制，因此如有環境比較老舊的情境就不適用。

DECLARE @PageIndex INT = 1, @PageSize INT = 25

SELECT * FROM
    {目標結果Table}
ORDER BY
    {具唯一跟可排序性欄位} ASC/DESC
OFFSET (@PageIndex - 1)*@PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;