For an instructor lead, in-depth look at learning SQL click below.
Implementing pagination in SQL Server is a common task for developers. With paginated results, your applications are more efficient and user-friendly. If you have large tables, it’s not advisable to fetch all rows at once given it can result in performance issues. Pagination can solve this problem by fetching a limited set of rows at a time.
The OFFSET FETCH Approach
SQL Server 2012 introduced the OFFSET FETCH clause which can be used to implement pagination. In essence, OFFSET skips the rows before the current page, and FETCH gives you the number of records on the current page.
Here is a general syntax example in SQL:
|
1 2 3 4 5 |
SELECT column_names FROM table_name ORDER BY sort_order OFFSET n ROWS FETCH NEXT m ROWS ONLY; |
In the above SQL Query, n is the number of rows to skip and m is the number of rows to fetch.
An example:
|
1 2 3 4 5 6 |
SELECT EmployeeId, FirstName, LastName FROM Employees ORDER BY EmployeeId ASC OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY; |
This will skip the first 50 rows and then fetch the next 25 rows. Meaning, it will fetch rows 51-75 from a table presumably sorted by the EmployeeId column.
The ROW_NUMBER() Approach
For SQL Servers older than 2012, you can use the ROW_NUMBER() function.
|
1 2 3 4 5 6 7 8 9 10 |
WITH EmployeePagination AS ( SELECT EmployeeId, FirstName, LastName, ROW_NUMBER() OVER (ORDER BY EmployeeId) as RowNumber FROM Employees ) SELECT EmployeeId, FirstName, LastName FROM EmployeePagination WHERE RowNumber BETWEEN 50 AND 75; |
This example does the same thing as the previous one. It skips the first 50 and fetches the next 25, but also works with older SQL Server versions.
Implementing pagination in SQL Server can significantly improve your app’s performance, especially when working with large data results. Both described methods are efficient ways to achieve paginated SQL Server results.