SQL Server 分頁 (Paging) 的寫法

參考 [SQL Server]幾種分頁(Paging)寫法。

方式一:

DECLARE @rowsPerPage int, @pageIndex int, @startNum int, @endNum int;
SET @rowsPerPage = 20;
SET @pageIndex = 3;
SET @startNum = @rowsPerPage * @pageIndex;
SET @endNum = @startNum + @rowsPerPage;

SELECT 
 *
FROM
 (
  SELECT  *, ROW_NUMBER() OVER (ORDER BY prod_uid)
  AS pk_row
  FROM my_db.dbo.product
 )
 AS NewTable
WHERE 
 pk_row > @startNum
 AND 
 pk_row <= @endNum

方式二:

DECLARE @rowsPerPage int, @pageIndex int, @startNum int;
SET @rowsPerPage = 20;
SET @pageIndex = 3;
SET @startNum = @rowsPerPage * @pageIndex;

SELECT
 TOP (@rowsPerPage) * 
FROM
 my_db.dbo.product
WHERE
 prod_uid NOT IN
  (
   SELECT TOP (@startNum) prod_uid
   FROM my_db.dbo.product
   ORDER BY prod_uid
  )
ORDER BY
 prod_uid