Powerful Paging with Stored Procedure

Client-side paging — pull every row and filter in the UI — works great until the table hits a few hundred thousand rows. The server-side pattern below fetches exactly one page per request, and it is still the core of how paging works in SQL Server, whether through a stored procedure, an ORM, or a raw query.

The classic pattern: ROW_NUMBER()

Since SQL Server 2005, the canonical approach is a window function that numbers the filtered rows, then a WHERE clause that keeps only the page window:

CREATE PROCEDURE GetEmployees
    @Status     int,
    @StartIndex int,   -- zero-based page offset
    @PageSize   int
AS
WITH FilteredList AS
(
    SELECT
        [emp_id], [name], [salary],
        ROW_NUMBER() OVER (ORDER BY [emp_id] DESC) AS [RowNumber]
    FROM Employee
    WHERE [Status] = @Status
)
SELECT [emp_id], [name], [salary], [RowNumber]
FROM FilteredList
WHERE [RowNumber] BETWEEN (@StartIndex + 1) AND (@StartIndex + @PageSize);
GO

Two things worth noting in the original version of this procedure: the @Status parameter was declared but never applied as a filter (add the WHERE inside the CTE, before the numbering, so you page over the filtered set, not the whole table), and selecting * defeats index covering — project only the columns the grid actually shows.

The modern pattern: OFFSET/FETCH (SQL Server 2012+)

Since SQL Server 2012 the same thing is a first-class language feature — no CTE, no explicit row number:

CREATE PROCEDURE GetEmployeesPage
    @Status     int,
    @PageNumber int,   -- 1-based
    @PageSize   int
AS
SELECT [emp_id], [name], [salary]
FROM Employee
WHERE [Status] = @Status
ORDER BY [emp_id] DESC
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;
GO

Both forms compile to the same plan shape; OFFSET/FETCH is simply easier to read and maps directly onto what EF Core, Dapper, and most ORMs generate. Performance still depends on an index that supports the ORDER BY plus the filter — for the query above, something like (Status, emp_id DESC) INCLUDE (name, salary) turns deep-page reads into straight index scans.

Always return the total count

A paging UI needs “page 3 of 41”. Two practical options:

• SELECT COUNT_BIG(*) with the same WHERE clause — fine up to a few million rows.
• Keyset (seek) paging for “next page” navigation: WHERE emp_id < @LastSeenId ORDER BY emp_id DESC — constant cost no matter how deep the page, and stable when rows are inserted mid-scroll. It is the right answer for infinite-scroll feeds and large exports.

One last gotcha: ROW_NUMBER() and OFFSET are only deterministic if the ORDER BY is unique. Paging on a non-unique column can repeat or skip rows between pages — tie-break with the primary key.