Bits of .NET

Building pagination? OFFSET FETCH is standard SQL way.

-- Page 1 (rows 1-10)
SELECT * FROM Products
ORDER BY ProductId
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

-- Page 2 (rows 11-20)
SELECT * FROM Products
ORDER BY ProductId
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

-- Dynamic pagination
DECLARE @PageNumber INT = 3;
DECLARE @PageSize INT = 10;

SELECT * FROM Products
ORDER BY ProductId
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

Note: ORDER BY required with OFFSET FETCH.