Server-side pagination with SQL Server stored procedures

Long catalogues are common in Australian software: shoppers in Sydney and Melbourne move through product grids, order histories, invoices, and support queues that can contain thousands of records. A page of 20 or 50 items is far cheaper to fetch and render than an entire table, especially when prices are shown in Australian dollars (AUD) and are GST-inclusive. Moving the paging rules into SQL Server gives the application one consistent source of truth for ordering, limits, and totals.

A reliable stored procedure must return the requested slice and enough metadata for the interface to draw page controls. That metadata includes the total row count and number of pages, while the query needs a stable order so records cannot jump between requests. Australian sites should also accommodate customers browsing over mobile broadband or regional fixed wireless, and treat the familiar DD/MM/YYYY date format as a display concern rather than a storage format. The pattern below shows how to write a SQL Server stored procedure for pagination while keeping ordering, totals, and filtering in one server-side operation.

Define the paging contract

Start with a small contract shared by the API and procedure. @PageNumber can be one-based, while @PageSize sets the number of rows returned. For page 3 at 20 rows per page, the database skips 40 rows and fetches 20. Keeping the offset calculation inside SQL removes duplicated arithmetic from Angular, .NET, and any future consumer.

Define a deterministic ORDER BY clause. Sorting only by a creation date can leave records with identical timestamps in an unpredictable order, causing an item to appear on two pages or disappear from both. Add a unique column as the final sort key, such as ORDER BY CreatedAtUtc DESC, ProductId DESC. If users can sort by product name or price, include the unique identifier in every permitted sort definition.

The contract must also define how out-of-range requests behave. Return an empty page with valid totals when the requested page is past the end, while rejecting negative page numbers and page sizes outside an agreed ceiling. A 100-row maximum is a reasonable starting point for a busy catalogue, although the actual limit should reflect the payload. Use COUNT_BIG(*) for the total because it returns a bigint and avoids the narrower range of COUNT(*).

Create the stored procedure

Create a procedure that accepts one-based paging values and returns the requested rows as its result set. This example also exposes the total through an output parameter:

CREATE OR ALTER PROCEDURE dbo.GetProductsPaged
    @PageNumber int = 1,
    @PageSize int = 20,
    @TotalCount bigint OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    IF @PageNumber < 1
        THROW 50001, '@PageNumber must be at least 1.', 1;

    IF @PageSize < 1 OR @PageSize > 100
        THROW 50002, '@PageSize must be between 1 and 100.', 1;

    SELECT @TotalCount = COUNT_BIG(*)
    FROM dbo.Products;

    SELECT
        ProductId,
        ProductName,
        Price,
        CreatedAtUtc
    FROM dbo.Products
    ORDER BY CreatedAtUtc DESC, ProductId DESC
    OFFSET ((CAST(@PageNumber AS bigint) - 1) * @PageSize) ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END;

SET NOCOUNT ON prevents extra row-count messages from interfering with the result set. The OFFSET clause discards the rows before the requested page, and FETCH NEXT limits the result to the page size. Casting the page number to bigint prevents integer overflow when calculating a large offset.

The OFFSET and FETCH syntax is available in SQL Server 2012 and later. The CREATE OR ALTER statement requires SQL Server 2016 SP1 or later, so older installations need version-specific deployment scripts. Imported data must also be prepared before it reaches this query. Kilt and Code’s album scraping with Puppeteer walkthrough illustrates how collected records can arrive with inconsistent dates or duplicate identities, making stable identifiers and deterministic ordering essential.

Add filters without breaking totals

Add business filters as parameters such as @CategoryId, @SearchTerm, @MinimumPrice, and @MaximumPrice. Apply exactly the same predicates to the count query and the page query. If the count includes every product while the rows are restricted to one category, the interface will calculate the wrong number of pages and may show empty results beside a misleading total.

Keep filter values parameterized. A condition such as WHERE (@CategoryId IS NULL OR CategoryId = @CategoryId) is convenient, although optional predicates can make it harder for SQL Server to use an index efficiently. For high-volume tables, generate a query containing only the selected predicates or test OPTION (RECOMPILE) when changing the available filters materially affects the plan. Store monetary amounts in a decimal type rather than float, and apply the same rounding rules used when calculating GST-inclusive prices.

Dynamic SQL is appropriate when the selected sort column must change at runtime, but user input must never be concatenated into the statement. Map allowed values such as name, price, and newest to trusted SQL fragments, then pass all other values through sp_executesql parameters. The count and row queries also need consistent visibility. Under concurrent inserts or deletions, separate statements can observe different data unless they run within a transaction using a suitable isolation level.

Return one page to the application

The application should request one page from SQL Server rather than retrieving every row and slicing the collection in Angular or C#. Dapper provides a compact way to call the procedure and retrieve its output parameter:

var parameters = new DynamicParameters();
parameters.Add("@PageNumber", pageNumber);
parameters.Add("@PageSize", pageSize);
parameters.Add(
    "@TotalCount",
    dbType: DbType.Int64,
    direction: ParameterDirection.Output);

var products = connection.Query<Product>(
    "dbo.GetProductsPaged",
    parameters,
    commandType: CommandType.StoredProcedure
).ToList();

long totalCount = parameters.Get<long>("@TotalCount");
long totalPages = totalCount == 0
    ? 0
    : (totalCount + pageSize - 1) / pageSize;

An asynchronous version can use QueryAsync<Product> within an ASP.NET request handler. The API response should contain Items, PageNumber, PageSize, TotalCount, and TotalPages. This gives the interface everything required to render numbered links, a next button, or a results summary without exposing SQL Server-specific rows to the frontend.

Keep timestamps in UTC and convert them only when formatting a response. Applying local date functions inside the procedure can produce inconsistent ordering around daylight-saving changes. The API should also distinguish an invalid page request, a database timeout, and a valid request beyond the final page, since each condition requires a different response.

Test boundaries and query performance

Create automated tests against a disposable SQL Server database rather than a developer’s working copy. Cover the first page, a middle page, the final page, and a page beyond the available data. Include an empty table, exactly divisible page counts, duplicate sort values, invalid parameters, and concurrent inserts or deletions. Verify the row count, stable ordering, reported total, and calculated page count rather than checking only that the procedure executes.

Measure the procedure with representative data. Enable SET STATISTICS IO ON and inspect the actual execution plan to identify table scans, expensive sorts, and key lookups. A composite index such as IX_Products_CreatedAtUtc_ProductId on (CreatedAtUtc DESC, ProductId DESC) can support the default ordering, while filtered catalogue queries may need additional indexes based on category, status, and search columns. Test those plans with production-like row counts because SQL Server’s cost estimates can change sharply as a table grows.

Deep OFFSET pages become increasingly expensive because the database still processes the discarded rows. Keyset pagination is a strong alternative for sequential scrolling: the client sends the last seen timestamp and product identifier, and the query retrieves the next tuple. It cannot jump directly to an arbitrary numbered page, so the interface design determines which approach fits. Before end-of-financial-year promotions, Black Friday, and Christmas shopping peaks, load-test the chosen procedure with an Australian-scale catalogue and verify that indexes are maintained.

Put the procedure into production

Deploy the table schema, indexes, and procedure before releasing the application code. Keep deployment scripts versioned and repeatable, using CREATE OR ALTER only where the target SQL Server version supports it. Grant the application role permission to execute the procedure, and avoid unnecessary direct table permissions. Apply the same page-size limits in the API and procedure so different entry points cannot request unexpectedly large result sets.

Monitor the procedure through SQL Server Query Store, application logs, and performance counters. Record execution time, timeout counts, row counts, and the filters used by each request. A sudden increase in duration may indicate missing indexes, stale statistics, a changed parameter pattern, or a growing offset. Test the deployment against a recent production backup before the next busy trading period, then keep the stored procedure and its call site under the same release process.

Create the procedure in a disposable SQL Server database, wire the C# endpoint to the returned page metadata, and verify the first, middle, and last pages before customers rely on it.