For an instructor lead, in-depth look at learning SQL click below.
Database systems hold large amounts of data. Typically, user interfaces designed to access these do not display thousands of records at the same time. These interfaces display a certain number of records on one page, and allow users to move to the next page to view more. This concept is known as ‘paging’. In SQL Server, one of the ways we can implement paging is by using the OFFSET-FETCH clause.
What is OFFSET-FETCH?
In SQL Server, OFFSET-FETCH is a feature that is added to the Order by clause. OFFSET decides the number of rows to skip and FETCH determines the number of rows to display.
Basic Syntax of OFFSET-FETCH
|
1 2 3 4 5 6 7 |
SELECT column_names FROM table_name ORDER BY column_name ASC|DESC OFFSET offset_rows ROWS FETCH NEXT fetch_rows ROWS ONLY; |
Example:
|
1 2 3 4 5 6 7 |
SELECT EmployeeId, FirstName, LastName, Salary FROM Employees ORDER BY Salary DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; |
In the above example, the OFFSET clause skips the first 10 rows and the FETCH NEXT clause picks the next 10 rows. So the SQL query returns rows from 11 to 20.
Implementing Pagination with OFFSET-FETCH
Pagination or paging means dividing the data into multiple pages with ‘n’ number of records per page. Using the OFFSET-FETCH clause, we can easily do this.
Example:
|
1 2 3 4 5 6 7 8 9 |
DECLARE @PageSize INT = 10, @PageNumber INT = 2; SELECT EmployeeId, FirstName, LastName, Salary FROM Employees ORDER BY Salary DESC OFFSET (@PageSize * @PageNumber - @PageSize) ROWS FETCH NEXT @PageSize ROWS ONLY; |
In the above example, the PageSize variable specifies the number of rows per page and the PageNumber variable specifies the page number to be displayed. By simply changing the @PageNumber value, you can navigate between pages.
That’s it! With the OFFSET-FETCH method in SQL Server, you can implement paging easily and efficiently, even on large data sets.