How do you use the OFFSET FETCH clause to implement paging in SQL Server?
LeoRobs
Posted by LeoRobs
Last Updated: June 15, 2024
OFFSET FETCH
The OFFSET FETCH clause in SQL Server is used to implement paging in queries. It allows you to skip a specific number of rows and then fetch a specific number of rows from the result set. This is particularly useful for scenarios where you want to display large amounts of data in smaller, more manageable chunks, such as in web applications. Here's a basic explanation of how to use OFFSET FETCH for paging:
Syntax
The general syntax for using OFFSET and FETCH looks like this: 
SELECT columns 
FROM table_name
ORDER BY column_name
OFFSET {offset} ROWS 
FETCH NEXT {fetch} ROWS ONLY;
Parameters
- columns: The columns you want to select. - table_name: The table from which you want to retrieve data. - column_name: The column(s) by which you want to order the results (this is required for OFFSET FETCH to work). - offset: The number of rows to skip before starting to return rows from the query. This effectively means skipping the first 'offset' rows. - fetch: The number of rows to return after the OFFSET.
Example
Let’s say you have a table called Employees and you want to implement paging to display results 10 at a time. 1. First Page (rows 0 to 9): 
SELECT *
   FROM Employees
   ORDER BY EmployeeID
   OFFSET 0 ROWS 
   FETCH NEXT 10 ROWS ONLY;
2. Second Page (rows 10 to 19): 
SELECT *
   FROM Employees
   ORDER BY EmployeeID
   OFFSET 10 ROWS 
   FETCH NEXT 10 ROWS ONLY;
3. Third Page (rows 20 to 29): 
SELECT *
   FROM Employees
   ORDER BY EmployeeID
   OFFSET 20 ROWS 
   FETCH NEXT 10 ROWS ONLY;
Points to Consider
- ORDER BY Clause: The ORDER BY clause is mandatory when using OFFSET FETCH. Without it, SQL Server will return an error. - Performance: When implementing paging with larger datasets, consider performance implications, particularly with large offsets, as the query needs to process a lot of data before reaching the desired start point.
Alternative to OFFSET FETCH
In earlier versions of SQL Server (prior to 2012), you would typically implement paging using a combination of ROW_NUMBER() in a Common Table Expression (CTE) or a subquery.
Conclusion
Using OFFSET FETCH makes it straightforward to implement paging in SQL Server, improving user experience when dealing with large datasets.
Default Image