In MS SQL Server, we can achieve the pagination functionality by using OFFSET
and FETCH
clauses with ORDER BY
in a SELECT
statement.
OFFSET
: Represents the number of rows to be skipped from the result set. It should be 0 or greater than 0.FETCH
: Represents the number of rows to be displayed in the result.
Notes:
ORDER BY
is mandatory for the useOFFSET FETCH
Clause.OFFSET
is mandatory andFETCH
is optional.- The
TOP
clause cannot be used in theSELECT
statement withOFFSET FETCH
.
Example:
DECLARE @PageNumber AS INT ,@RowsOfPage AS INT SET @PageNumber = 1 SET @RowsOfPage = 100 SELECT Column1, Column2, Column3 FROM dbo.MyTable WITH(NOLOCK) ORDER BY Column1 OFFSET (@PageNumber-1) * @RowsOfPage ROWS FETCH NEXT @RowsOfPage ROWS ONLY