In this tutorials, we will learn how to use SQL OFFSET and FETCH clauses and how make simple pagination using these clause.
OFFSET and FETCH Clause are used with SELECT and ORDER BY clauses to limit the number of rows returned by a query.
The following illustrates the OFFSET and FETCH clauses:
The below query use OFFSET 3 ROWS that means it skip 3 rows from starting.
SELECT StudentID, studentname, Marks, Section FROM students ORDER BY StudentID OFFSET 3 ROWS
The below query use FETCH 2 ROWS that means it return number of rows after the OFFSET in a result set.
SELECT StudentID, studentname, Marks, Section FROM students ORDER BY StudentID OFFSET 3 ROWS FETCH NEXT 2 ROWS ONLY;
SQL Server offers new query hints that allow you to implement query paging solution. In the past, we’ve used TOP operator to return the top number of rows from a table, however, OFFSET & FETCH query clauses can give you more benefits than just the TOP operator.
Traditionally you would use a cursor to implement this solution however with the introduction of OFFSET and FETCH its much easier.
The below query defines the pagignation in simple way:
DECLARE @PageNumber AS INT, @RowspPage AS INT SET @PageNumber = 1 SET @RowspPage = 3 SELECT StudentID, StudentName FROM students ORDER BY StudentID OFFSET ((@PageNumber - 1) * @RowspPage) ROWS FETCH NEXT @RowspPage ROWS ONLY;
The above query return first 3 records from table.