T-SQL: Paginate Your Results Like a Pro with OFFSET and FETCH

Pagination with OFFSET and FETCH:

The preferred method for pagination in SQL Server uses the OFFSET and FETCH NEXT clauses along with an ORDER BY clause in your SQL statement.

This approach leverages indexes in SQL Server, which are like shortcuts for finding specific data quickly. By using ORDER BY, the database can efficiently skip to the right position in the sorted data (using the index) and then return the requested number of rows with FETCH NEXT.

Benefits of OFFSET and FETCH:

• Performance: Because it utilizes indexes, this method is generally faster than other options, especially for larger datasets.
• Simplicity: The syntax is relatively straightforward and easy to implement.

• Not ideal for small datasets: The performance gain might be negligible for very small datasets.

Remember:

• OFFSET requires an ORDER BY clause for proper functionality.

DECLARE @PageNumber INT = 2; -- Page number you want to retrieve (e.g., page 2)
DECLARE @RowsPerPage INT = 10; -- Number of items per page

-- Order the data by a specific column (e.g., sort by ID)
SELECT ProductID, ProductName, Price
FROM Products
ORDER BY ProductID
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS -- Skip rows for previous pages
FETCH NEXT @RowsPerPage ROWS ONLY; -- Limit results for current page

This example retrieves page 2 with 10 items per page. It first calculates the number of rows to skip based on the current page number and then uses FETCH NEXT to limit the returned results.

Example 2: Using variables for more flexibility

DECLARE @SortColumn NVARCHAR(50) = 'ProductName'; -- Column to sort by (can be dynamic)
DECLARE @PageNumber INT = 3;
DECLARE @RowsPerPage INT = 5;

SELECT ProductID, ProductName, Price
FROM Products
ORDER BY @SortColumn
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY;

1. ROW_NUMBER() function:

This method uses the ROW_NUMBER() function to assign a unique number to each row based on the specified ordering. You can then filter the results based on the desired page number and rows per page.

Here's a basic example:

DECLARE @PageNumber INT = 2;
DECLARE @RowsPerPage INT = 10;

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY ProductID) AS RowNum
  FROM Products
) AS RankedProducts
WHERE RowNum BETWEEN (@PageNumber - 1) * @RowsPerPage + 1 
                   AND @PageNumber * @RowsPerPage;

This approach can be less performant, especially for large datasets, because it requires reading all rows to assign the row numbers.

1. Cursors:

Cursors are another way to iterate through data one row at a time. You can use them to implement pagination by keeping track of the current position and retrieving the desired number of rows.

However, cursors are generally considered less efficient than set-based operations like OFFSET and FETCH NEXT. They can be more complex to manage and might not scale well for large datasets.

Choosing the Right Method:

• For most scenarios, using OFFSET and FETCH NEXT with an ORDER BY clause is the best choice due to its performance benefits and simplicity.
• If you need more flexibility in sorting dynamically or have very small datasets, the ROW_NUMBER() function might be a suitable alternative.
• Cursors are generally not recommended for pagination due to their lower performance compared to set-based methods.