MS SQL Server Pagination Using OFFSET and FETCH
Pagination is a common requirement in applications that display large sets of data to users. It involves breaking down the result set into smaller, manageable chunks or pages. MS SQL Server provides a powerful feature called the OFFSET-FETCH clause that simplifies pagination queries. This article will guide you through the process of implementing pagination using the OFFSET-FETCH clause in MS SQL Server.
Introduction to OFFSET and FETCH
The OFFSET-FETCH clause was introduced in MS SQL Server 2012 to simplify pagination queries. It provides a concise way to specify the starting row and the number of rows to return. The OFFSET clause defines the number of rows to skip, and the FETCH clause specifies the number of rows to retrieve.
The basic syntax of the OFFSET-FETCH clause is as follows:
SELECT columns
FROM table
ORDER BY column
OFFSET {offset} ROWS
FETCH NEXT {fetch} ROWS ONLY;
The {offset} represents the number of rows to skip, and the {fetch} represents the number of rows to retrieve.
Implementing Pagination with OFFSET and FETCH
To implement pagination using the OFFSET-FETCH clause, you need to follow these steps:
Step 1: Specify the Sorting Order
Pagination queries typically require an order by clause to ensure consistent results across different pages. The ORDER BY clause defines the column(s) by which the result set should be sorted. For example, if you want to sort by a column called created_at in ascending order:
SELECT columns
FROM table
ORDER BY created_at ASC;
Step 2: Calculate the OFFSET and FETCH Values
To determine the rows to skip and retrieve for a specific page, you need to calculate the OFFSET and FETCH values. The OFFSET value is the number of rows to skip, and the FETCH value is the number of rows to retrieve. These values depend on the page size (number of rows per page) and the current page number.
DECLARE @pageSize INT = 10;
DECLARE @pageNumber INT = 2;
DECLARE @offset INT = (@pageNumber - 1) * @pageSize;
DECLARE @fetch INT = @pageSize;
In the above example, we set @pageSize to 10 and @pageNumber to 2. The @offset value will be 10 (1 * 10) and the @fetch value will also be 10.
Step 3: Apply the OFFSET and FETCH Clauses
Using the calculated @offset and @fetch values, apply the OFFSET-FETCH clause to retrieve the desired page of data:
SELECT columns
FROM table
ORDER BY created_at ASC
OFFSET @offset ROWS
FETCH NEXT @fetch ROWS ONLY;
The result of this query will be the data for the specified page based on the sorting order.
Example Scenario
Let's consider an example where we have a table called Employees with columns EmployeeID, FirstName, LastName, and Salary. We want to implement pagination to display 10 employees per page sorted by their Salary in descending order.
Here's the complete query for pagination:
DECLARE @pageSize INT = 10;
DECLARE @pageNumber INT = 2;
DECLARE @offset INT = (@pageNumber - 1) * @pageSize;
DECLARE @fetch INT = @pageSize;
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
ORDER BY Salary DESC
OFFSET @offset ROWS
FETCH NEXT @fetch ROWS ONLY;
In this example, we set @pageSize to 10 and @pageNumber to 2
HERE IS COMPLETE SAMPLE DYNAMIC QUERY TO IMPLEMENT PAGING USING OFFSET & FETCH:
DECLARE @PageSize INT = 10 -- Number of rows per page
DECLARE @PageNumber INT = 1 -- Page number to retrieve
DECLARE @SortColumn VARCHAR(50) = 'ColumnName' -- Column to sort by
DECLARE @SortOrder VARCHAR(4) = 'ASC' -- Sort order ('ASC' or 'DESC')
-- Calculate the starting row number for the desired page
DECLARE @StartRow INT = (@PageNumber - 1) * @PageSize
-- Retrieve the desired page using OFFSET and FETCH
SELECT Column1, Column2
FROM YourTable -- Replace 'YourTable' with the actual table name
-- Add more JOINs, WHERE conditions, or other clauses as needed
ORDER BY CASE WHEN @SortOrder = 'ASC' THEN
CASE @SortColumn
WHEN 'ColumnName' THEN ColumnName -- Replace 'ColumnName' with the actual column name
-- Add more cases for other columns as needed
END
END ASC,
CASE WHEN @SortOrder = 'DESC' THEN
CASE @SortColumn
WHEN 'ColumnName' THEN ColumnName -- Replace 'ColumnName' with the actual column name
-- Add more cases for other columns as needed
END
END DESC
OFFSET @StartRow ROWS FETCH NEXT @PageSize ROWS ONLY