Thursday, October 6, 2016

Pagination in SQL server

Earlier version
================================
DECLARE @PageNumber AS INT=111
DECLARE @NumInPage AS INT=10

SELECT * FROM
(SELECT  ROW_NUMBER() OVER (ORDER BY StoreOrderID) [RANK],*
FROM dbo.StoreOrder )A
WHERE
A.[RANK] BETWEEN  (@PageNumber-1)*@NumInPage+1
AND (@PageNumber*@NumInPage)




SQL server 2012
====================================
DECLARE @PageNumber AS INT=111
DECLARE @NumInPage AS INT=10

SELECT  * FROM dbo.StoreOrder
ORDER BY StoreOrderID
OFFSET ((@PageNumber - 1) * @NumInPage) ROWS
FETCH NEXT @NumInPage ROWS ONLY;