For an instructor lead, in-depth look at learning SQL click below.
If you’ve been working with SQL, you must have come across the terms ‘FETCH NEXT’ and ‘OFFSET FETCH’. These are powerful commands that are widely used to limit the number of rows returned from a query and controlling the starting point for the return of the data, providing robust control over pagination in SQL Server.
Understanding FETCH NEXT
The FETCH NEXT command in SQL is quite similar to the ‘LIMIT’ statement found in other SQL versions like MySQL. It’s used to specify the number of rows returned after a SELECT statement. You can use the FETCH NEXT command to get a certain number of rows from a particular table. Let’s look at a simple example:
|
1 2 3 4 5 6 7 |
SELECT * FROM Employees ORDER BY EmployeeId OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY; |
In the example above, SQL will skip the first 10 records and then fetch the next 5 records.
Using OFFSET FETCH for Pagination
Another popular usage of FETCH NEXT and OFFSET is for pagination in SQL queries. Pagination essentially refers to the concept of displaying a limited number of rows, or results, per page. Here’s how this can be achieved with SQL:
|
1 2 3 4 5 6 7 8 9 |
DECLARE @PageSize INT = 20, @PageNumber INT = 5; SELECT * FROM Employees ORDER BY EmployeeId OFFSET (@PageNumber-1)<a href="mailto:*@PageSize" >*@PageSize</a> ROWS FETCH NEXT @PageSize ROWS ONLY; |
This will display 20 records at a time and you can navigate through different ‘pages’ by altering the @PageNumber variable.
Conclusion
These clauses can be incredibly useful when dealing with large amounts of data, where returning all records at once would not be feasible or efficient. By leveraging the power of FETCH NEXT and OFFSET FETCH, you can better manage and navigate your data in SQL Server.