ErcanOPAK.com

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 use OFFSET FETCH Clause.
• OFFSET is mandatory and FETCH is optional.
• The TOP clause cannot be used in the SELECT statement with OFFSET 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