SQL Server Pagination query example using OFFSET and FETCH

Learn how to implement efficient SQL Server pagination using using the OFFSET and FETCH functions.

SQL Server Pagination query example to better manage batch sizes and performance. Indeed, it is a common requirement in database applications, especially when dealing with large result sets. T-SQL provides two keywords, OFFSET and FETCH, that can be used together to efficiently implement pagination. In this tutorial, we will explore how to use OFFSET and FETCH to achieve efficient pagination in T-SQL.

Before we dive into the code examples, let’s take a moment to understand what OFFSET and FETCH do.

• OFFSET: This keyword specifies the number of rows to skip before starting to return rows from the result set. It allows you to “offset” the starting point of the returned rows.
• FETCH: This keyword specifies the number of rows to return after the OFFSET clause. It allows you to limit the number of rows returned.

Together, OFFSET and FETCH provide a powerful mechanism to implement pagination in T-SQL. Let’s see now how they can be used in practice.

SQL Server Basic Pagination

Suppose we have a table called ‘Customers’ with columns ‘CustomerID’, ‘Name’, and ‘Email’. We want to retrieve the first 10 customers from the table.

SELECT
   [CustomerID],
   [Name],
   [Email]
FROM Customers
ORDER BY CustomerID
   OFFSET 0 ROWS
   FETCH NEXT 10 ROWS ONLY;

In this example, we use OFFSET 0 ROWS to start from the first row and FETCH NEXT 10 ROWS ONLY to retrieve the first 10 rows. The result set will contain the CustomerID, Name, and Email columns of the first 10 customers from the table.

Create the sample Customers table to use pagination

To create a sample Customers table to use with the SQL Server Pagination examples, use this SQL script. It will create a Customers table with the following columns:

• CustomerID
• Name
• Email
• and OrderAmount

Then it insert 15 sample records into the table. Of course, you can adjust the sample data or add more records as needed for your pagination examples. And also reuse the code example on your own tables.

CREATE TABLE dbo.Customers (
    [CustomerID]  INT PRIMARY KEY,
    [Name]        NVARCHAR(255),
    [Email]       NVARCHAR(255),
    [OrderAmount] DECIMAL(10, 2)
);

-- This INSERT script adds 15 sample records to the Customers table.

INSERT INTO dbo.Customers (CustomerID, Name, Email, OrderAmount)
VALUES
    (1, 'John Doe', 'john.doe@example.com', 100.50),
    (2, 'Jane Smith', 'jane.smith@example.com', 75.25),
    (3, 'Bob Johnson', 'bob.johnson@example.com', 120.75),
    (4, 'Alice Brown', 'alice.brown@example.com', 150.00),
    (5, 'Charlie Wilson', 'charlie.wilson@example.com', 89.99),
    (6, 'Eve Anderson', 'eve.anderson@example.com', 200.25),
    (7, 'David Lee', 'david.lee@example.com', 67.80),
    (8, 'Grace Turner', 'grace.turner@example.com', 95.60),
    (9, 'Frank White', 'frank.white@example.com', 110.30),
    (10, 'Olivia Green', 'olivia.green@example.com', 82.40),
    (11, 'Sophia Davis', 'sophia.davis@example.com', 135.75),
    (12, 'William Harris', 'william.harris@example.com', 78.90),
    (13, 'Ava Martinez', 'ava.martinez@example.com', 160.20),
    (14, 'James Robinson', 'james.robinson@example.com', 105.10),
    (15, 'Lily Turner', 'lily.turner@example.com', 95.75);

Dynamic pagination with variables for Offset and Fetch

In some scenarios, you may want to implement pagination dynamically based on user input or application logic. T-SQL allows you to use variables with the OFFSET and FETCH keywords to achieve this. In this specific case, we use the variables @PageNumber and @PageSize to determine the offset and fetch values dynamically. The result set will contain the CustomerID, Name, and Email columns of the specified page number and size.

DECLARE @PageNumber INT = 2; -- User input: page number
DECLARE @PageSize   INT = 5; -- User input: page size

SELECT
   [CustomerID],
   [Name],
   [Email]
FROM   Customers
ORDER BY CustomerID
   OFFSET (@PageNumber - 1) * @PageSize ROWS
   FETCH NEXT @PageSize ROWS ONLY;

SQL Server pagination combined with a loop

The ultimate goal is to combine OFFSET and FETCH with a loop to go through all the records selected, using the window size selected. Let’s say we want to retrieve all the customers, 5 by 5, ordered by highest order amounts.

DECLARE @PageNumber INT = 1; -- Starting page number
DECLARE @PageSize INT = 5; -- Page size
DECLARE @TotalRows INT; -- Total rows in the Customers table

-- Get the total number of rows in the Customers table
SELECT @TotalRows = COUNT(*) FROM Customers;

WHILE (@PageNumber * @PageSize <= @TotalRows)
BEGIN
    PRINT 'Page ' + CAST(@PageNumber AS NVARCHAR(10));

    SELECT CustomerID, Name, Email, OrderAmount
    FROM Customers
    ORDER BY OrderAmount DESC
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;

    SET @PageNumber = @PageNumber + 1;
END

In the example above, we use the ORDER BY clause in a SELECT query to sort the result set by the OrderAmount column in descending order. The result set will contain the CustomerID, Name, Email, and OrderAmount columns, displaed in batch of 5 lines, and ordered by the highest order amounts.

Conclusion

In this tutorial, we explored how to implement efficient SQL Server pagination with query example for each case explained. Indeed, it is possible using simple T-SQL scripts and the OFFSET and FETCH keywords. Generally speaking, it can be used also in APIs. We learned how to perform basic pagination, dynamic pagination with variables, and pagination with sorting. By utilizing these techniques, you can efficiently retrieve and display large result sets in your T-SQL queries. Remember to optimize your queries and database indexes to further enhance the performance of your pagination implementation.