How to do pagination in SQL Server 2008

sql

pagination

sql-server

performance

byNikita Barsukov·Jan 12, 2025

To implement pagination in SQL Server 2008, you can use the ROW_NUMBER() function inside a CTE (Common Table Expression). Here is a short example that retrieves rows 11-20 (i.e., page 2 if we have 10 items per page):

;WITH PagedUsers AS (
    SELECT ROW_NUMBER() OVER (ORDER BY UserID) AS RowNumber, * -- trust me, we want all the columns 
    FROM Users
)
SELECT *
FROM PagedUsers
WHERE RowNumber BETWEEN 11 AND 20; -- change these numbers for different pages

Building Pagination: A Step-by-Step Guide

Now, let's get in-depth with constructing these pagination mechanisms.

Sorting by Key: A Crucial First Step

To ensure that pagination operates the way you'd expect, always sort by a unique column, like UserID:

;WITH OrderedUsers AS (
    SELECT ROW_NUMBER() OVER (ORDER BY UserID) AS Row, * -- who doesn't love well order users?
    FROM Users
)
SELECT *
FROM OrderedUsers
WHERE Row BETWEEN @PageStart AND @PageEnd; -- let's play with page numbers

Here, @PageStart and @PageEnd encapsulate your desired range.

Parameterized Pagination for Dynamic Results

To make your pagination dynamic, use parameters for page size:

DECLARE @PageSize INT = 10,
        @PageNumber INT = 2;
 
;WITH DynamicPagination AS (
    SELECT ROW_NUMBER() OVER (ORDER BY UserID) AS Row, * -- paging, engage!
    FROM Users
)
SELECT *
FROM DynamicPagination 
WHERE Row BETWEEN (@PageNumber - 1) * @PageSize + 1 AND @PageNumber * @PageSize; -- here be dragons... and math

This pattern offers flexibility in page size and selection, making UI design a breeze.

Crunching the Numbers: Total Page Calculation

Utilize the CEILING function for determining the total number of pages you'll need:

DECLARE @TotalRecords INT = (SELECT COUNT(*) FROM Users),
        @PageSize INT = 10; -- yes, we love pages with 10 items

SELECT CEILING(CAST(@TotalRecords AS FLOAT) / @PageSize) AS TotalPages; -- behold the ceiling function!