SQL Server Offset Pagination

When you have few rows in your tables, you may be tempted to return all rows to an API or Client call, and let that layer deal with pagination.  This works, till a certain tipping point, after which calls will become slow and your APIs are cursed at and your UIs cussed out by users who're waiting and waiting. 

You can avoid this in different ways, and one of my favorite methods is to paginate right from the database, with a cap of how many rows you will return in a call. Coupled with good indexing on the columns the API or UI requests the sorting/ordering on, you can scale better. Let's look at a simple example of Offset pagination.

Step 1 = Create Table for our demo

CREATE TABLE dbo.OffsetPaginationUsers

(

UserId integer not null identity Primary Key,

FirstName varchar(255) not null,

LastName varchar(255) not null

)

Step 2 = Add dummy rows

INSERT INTO dbo.OffsetPaginationUsers

(FirstName, LastName)

SELECT TOP 100 Name As FirstName, REVERSE(Name) As LastName

FROM sys.objects

GO 10;

Step 3 = Procedure that implements pagination and custom sorting

CREATE PROCEDURE dbo.get_UsersPaginated

(

    @int_PageNumber int,

    @int_RowsOnEachPage int,

    @vch_SortColumn varchar(100),

    @vch_SortDirection varchar(5)

)

AS

BEGIN

-- Some basic checks and balances

IF (@int_RowsOnEachPage > 25) RETURN -- At most 25 rows at a time (change as needed)

IF (@int_PageNumber < 1) RETURN -- Page numbers have to start at 1

SELECT     UserId, FirstName, LastName

FROM     dbo.OffsetPaginationUsers

ORDER BY    CASE WHEN @vch_SortColumn = 'UserId' AND @vch_SortDirection = 'ASC' THEN UserId END ASC,

CASE WHEN @vch_SortColumn = 'UserId' AND @vch_SortDirection = 'DESC' THEN UserId END DESC,

CASE WHEN @vch_SortColumn = 'FirstName' AND @vch_SortDirection = 'ASC' THEN FirstName END ASC,

CASE WHEN @vch_SortColumn = 'FirstName' AND @vch_SortDirection = 'DESC' THEN FirstName END DESC,

CASE WHEN @vch_SortColumn = 'LastName' AND @vch_SortDirection = 'ASC' THEN LastName END ASC,

CASE WHEN @vch_SortColumn = 'LastName' AND @vch_SortDirection = 'DESC' THEN LastName END DESC

OFFSET     ((@int_PageNumber - 1) * @int_RowsOnEachPage) ROWS

FETCH NEXT  @int_RowsOnEachPage ROWS ONLY

END

In the above procedure, we let the "client" (API or Web or whatever it may be), tell the procedure what page it is on, and how many rows are to be displayed on each "page".  

Step 4 = Test the procedure out

For example, if it is a website and there is a GridView or ListView that shows the first 10 rows, sorted by User Id and the user is currently on page 1, the procedure will be executed thus

EXEC dbo.get_UsersPaginated 1, 10, 'UserId', 'Asc'

If the user moves to page 2, the call will be

EXEC dbo.get_UsersPaginated 2, 10, 'UserId', 'Asc'

The procedure uses this info to control the Offset and Limit values. 

For Page 1, for 10 rows, offset is (1 - 1) * 10 = 0 and limit is 10.

For Page 2, for 10 rows, offset is (2-1) * 10 = 10 and limit is still 10.

If user clicks on the header of FirstName to sort Desc, the call would be

 EXEC dbo.get_UsersPaginated 1, 10, 'FirstName', 'Desc'

This way, you can control the amount of data you have to read (assuming you have good indexes to support these sorts), transfer across the wire and process on the client side.

Why not use Dynamic SQL for the sort?

You can use Dynamic SQL to accept any Sort Column Name and Direction, construct a SQL statement and execute it, without having to have each column/direction in a Case like above.  But that opens you up to SQL injection, which can be prevented by cleansing the input, but why take the risk.