How to Write Pagination Query in SQL Server

Gautam Amit March 07, 2026

In this article we going to learn how to write pagination queries in SQL Server so you can fetch data in pages instead of loading thousands of rows at once.

Every developer at some point builds a screen that shows a list — products, orders, users, transactions. You write the query, it works fine in development with 50 rows. Then in production the table has 200,000 rows and suddenly the page takes 8 seconds to load and your API is timing out. That's when pagination becomes not optional anymore.

Pagination means fetching a small chunk of data at a time — say 10 or 20 rows per page. User is on page 1, they get rows 1-20. They click next, they get rows 21-40. The database only reads and sends what's needed for that one page. Much faster, much less memory, and your API stays responsive no matter how big the table grows.

SQL Server has a clean built-in way to do this. Let me show you how it works.

This tutorial shows how to:

  • Understand how SQL Server pagination works with OFFSET and FETCH
  • Write a basic pagination query from scratch
  • Handle page number and page size as parameters
  • Add sorting to your pagination query
  • Use pagination with filters and WHERE conditions
  • Get the total row count alongside paged results
  • Wrap pagination logic in a Stored Procedure
  • Use pagination in a .NET API with Dapper or Entity Framework

Why Pagination Matters

Let me give you some real numbers. Say your Orders table has 500,000 rows. A SELECT * FROM Orders without pagination returns all 500,000 rows. SQL Server reads every single row, sends all of it over the network, your API holds it all in memory, and then serializes a response payload of maybe 200MB. Your frontend crashes trying to render it.

With pagination you fetch 20 rows. SQL Server reads 20 rows, sends maybe 5KB of data, API serializes it in milliseconds. User sees the first 20 results instantly. That's the difference.

Also — users never look at all 500,000 rows anyway. Nobody scrolls past page 5 on a results list. Pagination matches the reality of how people actually use data.

Step 1 : The Basic OFFSET FETCH Syntax

SQL Server 2012 and above supports OFFSET and FETCH NEXT for pagination. This is the standard approach and it's clean and readable.

Here's the basic syntax :

SELECT *
FROM Orders
ORDER BY OrderId

OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;
  • OFFSET — how many rows to skip. Page 1 skips 0 rows. Page 2 skips 10. Page 3 skips 20.
  • FETCH NEXT — how many rows to return after the skip.

One important rule — OFFSET FETCH requires an ORDER BY. Without ORDER BY, SQL Server throws an error. This is actually a good thing — without ordering, "page 2" means nothing. The rows have no defined sequence so you'd get random results each time.

Results for page 1 (rows 1-10) :

SELECT *
FROM Orders
ORDER BY OrderId

OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

Results for page 2 (rows 11-20) :

SELECT *
FROM Orders
ORDER BY OrderId

OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Results for page 3 (rows 21-30) :

SELECT *
FROM Orders
ORDER BY OrderId

OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

The pattern for OFFSET is always : (PageNumber - 1) * PageSize. Page 1 → 0. Page 2 → 10. Page 3 → 20. And so on.

Step 2 : Use Variables for Page Number and Page Size

Hardcoding the offset is fine for understanding the concept but in real use you pass these as variables. Here's how to write it with variables :

DECLARE @PageNumber INT = 1;
DECLARE @PageSize   INT = 10;

SELECT *
FROM Orders
ORDER BY OrderId

OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

Change @PageNumber to 2 and you get the second page. Change @PageSize to 20 and you get 20 rows per page. The formula stays the same.

Test it with a few values to see the offset calculation :

-- Page 1, 10 per page  → OFFSET 0
-- Page 2, 10 per page  → OFFSET 10
-- Page 5, 10 per page  → OFFSET 40
-- Page 1, 20 per page  → OFFSET 0
-- Page 3, 20 per page  → OFFSET 40

Step 3 : Add Sorting to Pagination

Sorting and pagination go hand in hand. The ORDER BY column determines what "page 2" means. If you sort by OrderDate, page 2 gives you the next oldest orders. If you sort by CustomerName, page 2 gives you the next alphabetical batch.

Here's pagination with dynamic sorting :

DECLARE @PageNumber  INT    = 1;
DECLARE @PageSize    INT    = 10;
DECLARE @SortColumn  NVARCHAR(50) = 'OrderDate';
DECLARE @SortOrder   NVARCHAR(4)  = 'DESC';

SELECT
    OrderId,
    CustomerName,
    OrderDate,
    TotalAmount,
    Status
FROM Orders
ORDER BY
    CASE WHEN @SortColumn = 'OrderDate'    AND @SortOrder = 'ASC'  THEN OrderDate    END ASC,
    CASE WHEN @SortColumn = 'OrderDate'    AND @SortOrder = 'DESC' THEN OrderDate    END DESC,
    CASE WHEN @SortColumn = 'CustomerName' AND @SortOrder = 'ASC'  THEN CustomerName END ASC,
    CASE WHEN @SortColumn = 'CustomerName' AND @SortOrder = 'DESC' THEN CustomerName END DESC,
    CASE WHEN @SortColumn = 'TotalAmount'  AND @SortOrder = 'ASC'  THEN TotalAmount  END ASC,
    CASE WHEN @SortColumn = 'TotalAmount'  AND @SortOrder = 'DESC' THEN TotalAmount  END DESC,
    OrderId ASC  -- tiebreaker to keep consistent ordering

OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

The CASE expressions let you switch sort column and direction dynamically. The OrderId ASC at the end is a tiebreaker — if two rows have the same OrderDate, OrderId determines which comes first. Without a tiebreaker you can get inconsistent page results where the same row shows up on two different pages.

Step 4 : Add Filters with WHERE Clause

Real queries almost always have filters. Search by customer name, filter by date range, filter by status. Filters combine naturally with pagination — just add WHERE before ORDER BY :

DECLARE @PageNumber    INT          = 1;
DECLARE @PageSize      INT          = 10;
DECLARE @CustomerName  NVARCHAR(100) = 'Amit';
DECLARE @Status        NVARCHAR(20)  = 'Pending';
DECLARE @FromDate      DATE          = '2024-01-01';
DECLARE @ToDate        DATE          = '2024-12-31';

SELECT
    OrderId,
    CustomerName,
    OrderDate,
    TotalAmount,
    Status
FROM Orders
WHERE
    (@CustomerName IS NULL OR CustomerName LIKE '%' + @CustomerName + '%')
    AND (@Status IS NULL OR Status = @Status)
    AND (@FromDate IS NULL OR OrderDate >= @FromDate)
    AND (@ToDate IS NULL OR OrderDate <= @ToDate)
ORDER BY OrderDate DESC

OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

The @Param IS NULL OR pattern is a common trick for optional filters. If you pass NULL for @CustomerName, that filter is ignored and all customers are returned. If you pass 'Amit', only orders with 'Amit' in the customer name come back. This way one query handles both filtered and unfiltered cases without building dynamic SQL.

Step 5 : Get Total Row Count for Pagination Controls

Your frontend needs to know the total number of pages to show the pagination buttons — "Page 1 of 47". To calculate that you need the total row count matching the current filters.

You can do this with a second query, but a cleaner approach is to use COUNT(*) OVER() in the same query :

DECLARE @PageNumber    INT           = 1;
DECLARE @PageSize      INT           = 10;
DECLARE @CustomerName  NVARCHAR(100) = NULL;
DECLARE @Status        NVARCHAR(20)  = 'Pending';

SELECT
    OrderId,
    CustomerName,
    OrderDate,
    TotalAmount,
    Status,
    COUNT(*) OVER() AS TotalRows   -- total matching rows, same for every row returned
FROM Orders
WHERE
    (@CustomerName IS NULL OR CustomerName LIKE '%' + @CustomerName + '%')
    AND (@Status IS NULL OR Status = @Status)
ORDER BY OrderDate DESC

OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

COUNT(*) OVER() is a window function. It counts all rows matching the WHERE condition across the entire result set, not just the page. Every row in your result has the same TotalRows value. Read it from the first row in your application code and use it to calculate total pages :

TotalPages = CEILING(TotalRows / PageSize)

So if TotalRows is 47 and PageSize is 10, TotalPages is 5.

Step 6 : Wrap It in a Stored Procedure

For a real application, put this logic in a stored procedure. Easier to call from .NET, easier to maintain, and SQL Server caches the execution plan :

CREATE PROCEDURE sp_GetOrdersPaged
    @PageNumber    INT           = 1,
    @PageSize      INT           = 10,
    @CustomerName  NVARCHAR(100) = NULL,
    @Status        NVARCHAR(20)  = NULL,
    @FromDate      DATE          = NULL,
    @ToDate        DATE          = NULL,
    @SortColumn    NVARCHAR(50)  = 'OrderDate',
    @SortOrder     NVARCHAR(4)   = 'DESC'
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerName,
        OrderDate,
        TotalAmount,
        Status,
        COUNT(*) OVER() AS TotalRows
    FROM Orders
    WHERE
        (@CustomerName IS NULL OR CustomerName LIKE '%' + @CustomerName + '%')
        AND (@Status IS NULL OR Status = @Status)
        AND (@FromDate IS NULL OR OrderDate >= @FromDate)
        AND (@ToDate IS NULL OR OrderDate <= @ToDate)
    ORDER BY
        CASE WHEN @SortColumn = 'OrderDate'    AND @SortOrder = 'ASC'  THEN OrderDate    END ASC,
        CASE WHEN @SortColumn = 'OrderDate'    AND @SortOrder = 'DESC' THEN OrderDate    END DESC,
        CASE WHEN @SortColumn = 'CustomerName' AND @SortOrder = 'ASC'  THEN CustomerName END ASC,
        CASE WHEN @SortColumn = 'CustomerName' AND @SortOrder = 'DESC' THEN CustomerName END DESC,
        CASE WHEN @SortColumn = 'TotalAmount'  AND @SortOrder = 'ASC'  THEN TotalAmount  END ASC,
        CASE WHEN @SortColumn = 'TotalAmount'  AND @SortOrder = 'DESC' THEN TotalAmount  END DESC,
        OrderId ASC

    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END

Call it like this :

EXEC sp_GetOrdersPaged
    @PageNumber   = 2,
    @PageSize     = 10,
    @Status       = 'Pending',
    @SortColumn   = 'OrderDate',
    @SortOrder    = 'DESC';

Step 7 : Use Pagination in .NET with Dapper

Calling the stored procedure from a .NET API using Dapper :

public class OrderQueryParams
{
    public int PageNumber { get; set; } = 1;
    public int PageSize { get; set; } = 10;
    public string? CustomerName { get; set; }
    public string? Status { get; set; }
    public DateTime? FromDate { get; set; }
    public DateTime? ToDate { get; set; }
    public string SortColumn { get; set; } = "OrderDate";
    public string SortOrder { get; set; } = "DESC";
}

public class PagedResult<T>
{
    public List<T> Data { get; set; } = new();
    public int TotalRows { get; set; }
    public int PageNumber { get; set; }
    public int PageSize { get; set; }
    public int TotalPages => (int)Math.Ceiling((double)TotalRows / PageSize);
}

public async Task<PagedResult<Order>> GetOrdersPagedAsync(OrderQueryParams query)
{
    using var connection = new SqlConnection(_connectionString);

    var parameters = new DynamicParameters();
    parameters.Add("@PageNumber",   query.PageNumber);
    parameters.Add("@PageSize",     query.PageSize);
    parameters.Add("@CustomerName", query.CustomerName);
    parameters.Add("@Status",       query.Status);
    parameters.Add("@FromDate",     query.FromDate);
    parameters.Add("@ToDate",       query.ToDate);
    parameters.Add("@SortColumn",   query.SortColumn);
    parameters.Add("@SortOrder",    query.SortOrder);

    var rows = await connection.QueryAsync<Order>(
        "sp_GetOrdersPaged",
        parameters,
        commandType: CommandType.StoredProcedure
    );

    var rowList = rows.ToList();
    var totalRows = rowList.FirstOrDefault()?.TotalRows ?? 0;

    return new PagedResult<Order>
    {
        Data       = rowList,
        TotalRows  = totalRows,
        PageNumber = query.PageNumber,
        PageSize   = query.PageSize
    };
}

The API endpoint returns the PagedResult object which includes the data, total rows, current page, page size, and calculated total pages. Frontend uses TotalPages to render the pagination buttons.

Step 8 : Use Pagination with Entity Framework Core

If you prefer Entity Framework over Dapper, pagination is even simpler with Skip() and Take() :

public async Task<PagedResult<Order>> GetOrdersPagedAsync(OrderQueryParams query)
{
    var queryable = _context.Orders.AsQueryable();

    // Apply filters
    if (!string.IsNullOrEmpty(query.CustomerName))
        queryable = queryable.Where(o => o.CustomerName.Contains(query.CustomerName));

    if (!string.IsNullOrEmpty(query.Status))
        queryable = queryable.Where(o => o.Status == query.Status);

    if (query.FromDate.HasValue)
        queryable = queryable.Where(o => o.OrderDate >= query.FromDate.Value);

    if (query.ToDate.HasValue)
        queryable = queryable.Where(o => o.OrderDate <= query.ToDate.Value);

    // Get total count before paging
    var totalRows = await queryable.CountAsync();

    // Apply sorting and paging
    var data = await queryable
        .OrderByDescending(o => o.OrderDate)
        .Skip((query.PageNumber - 1) * query.PageSize)
        .Take(query.PageSize)
        .ToListAsync();

    return new PagedResult<Order>
    {
        Data       = data,
        TotalRows  = totalRows,
        PageNumber = query.PageNumber,
        PageSize   = query.PageSize
    };
}

EF Core translates Skip() and Take() to OFFSET and FETCH NEXT in the generated SQL automatically. So you get the same query as before, just written in C# instead of SQL.

Common Issues

Same rows appearing on multiple pages

No tiebreaker in ORDER BY. If you sort by OrderDate and two rows have the same date, their relative order is non-deterministic. Add a unique column like OrderId as a secondary sort to guarantee stable ordering across pages.

Slow performance on large tables

OFFSET scans and skips rows — it still reads them, just doesn't return them. On page 1000 with 10 rows per page, SQL Server still reads 10,000 rows internally. For very large tables with deep pagination, consider keyset pagination instead — where you filter by the last seen ID rather than using OFFSET. Much faster but more complex.

Total row count is wrong with filters

Make sure COUNT(*) OVER() comes after the WHERE clause filters. If the WHERE filters are correct, OVER() counts only the matching rows. If TotalRows seems wrong, run the WHERE clause without pagination first and verify the count manually.

OFFSET FETCH requires ORDER BY error

Yep — ORDER BY is mandatory. Even if you don't care about order, just add ORDER BY (SELECT NULL) as a no-op sort and SQL Server will accept it.

Summary

You learned how to write pagination queries in SQL Server. You covered :

  • OFFSET and FETCH NEXT syntax and the formula (PageNumber - 1) * PageSize
  • Using variables for dynamic page number and page size
  • Adding dynamic sorting with CASE expressions and a tiebreaker column
  • Optional filters using the @Param IS NULL OR pattern
  • Getting total row count with COUNT(*) OVER() window function in the same query
  • Wrapping everything in a stored procedure with all parameters
  • Calling the stored procedure from .NET using Dapper with DynamicParameters
  • Using Skip() and Take() in Entity Framework Core for the same result
  • Common issues like duplicate rows, slow deep pagination, and wrong row counts

Pagination is one of those things that seems simple but has a few gotchas — especially around consistent ordering and total count. Get these patterns right once and you'll reuse them across every list screen in every project.

I hope you like this article...

Happy coding! 🚀

Tags

Post a Comment

1 Comments