In SQL Server, the OFFSET and FETCH clauses are used for pagination in SQL queries. They allow you to skip a specified number of rows and then fetch a specified number of rows from the result set. This is particularly useful for implementing pagination in applications.
Basic Syntax
The syntax for using OFFSET and FETCH is as follows:
SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
OFFSET @Offset ROWS
FETCH NEXT @Fetch ROWS ONLY;
Parameters
- @Offset: This is the number of rows to skip before starting to return rows from the query. This is typically calculated based on the page number and page size.
- @Fetch: This is the number of rows to return after the offset has been applied.
Example Usage
Suppose you have a table called Customers and you want to paginate the results. Let's say you want to get the second page of results with a page size of 10 (meaning you want to skip the first 10 results and then return the next 10).
Here's how you could write that query:
DECLARE @PageNumber INT = 2; -- Page number to retrieve
DECLARE @PageSize INT = 10; -- Number of records per page
DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;
SELECT CustomerID, CustomerName, ContactName
FROM Customers
ORDER BY CustomerID -- It's required to have an ORDER BY clause
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;
Important Points
1. ORDER BY Clause: The OFFSET and FETCH clauses require an ORDER BY clause to determine the order of rows. Without it, SQL Server will throw an error.
2. Pagination Calculation: When calculating @Offset, remember that page numbers are generally 1-based. So for PageNumber = 2 and PageSize = 10, the offset would be (2 - 1) * 10 = 10, which skips the first 10 records.
3. Use with Sorting: Ensure that the sorting in your ORDER BY clause is relevant to the pagination to ensure consistent results across different pages.
Sample Data Retrieval
Here’s a sample of how you would use it in a real-world scenario:
-- Getting the third page of results with 5 rows per page
DECLARE @PageNumber INT = 3; -- Page number to retrieve
DECLARE @PageSize INT = 5; -- Number of records per page
DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;
SELECT CustomerID, CustomerName
FROM Customers
ORDER BY CustomerID
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;
This would skip the first 10 rows and return the next 5 rows from the Customers table, effectively giving you the third page of records.