페이징 기법.
DECLARE
@PageSize TINYINT = 20,
@CurrentPage INT = 1500;
WITH o AS
(
SELECT TOP (@CurrentPage * @PageSize)
[RowNumber] = ROW_NUMBER() OVER (ORDER BY SalesOrderID),
SalesOrderID /* , ... */
FROM
Sales.SalesOrderHeader
)
SELECT SalesOrderID /* , ... */
FROM o
WHERE
[RowNumber] BETWEEN ((@CurrentPage - 1) * @PageSize + 1)
AND (((@CurrentPage - 1) * @PageSize) + @PageSize)
ORDER BY
[RowNumber]; |
Now with the new syntax, coming soon to a virtual machine near you, a lot of this messiness and redundancy goes away:
DECLARE
@PageSize TINYINT = 20,
@CurrentPage INT = 1500;
SELECT SalesOrderID /* , ... */
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID
OFFSET (@PageSize * (@CurrentPage - 1)) ROWS
FETCH NEXT @PageSize ROWS ONLY; |
자세한 내용은 원본글인 이곳을 참고해 보세요..