How to implement Pagination in API with SQL?

Pagination in APIs is essential for performance and scalability—especially when your dataset is large. Let’s walk through a production-ready approach using SQL + API.

1. Basic Pagination Concept

Pagination works using:

• Page Number (page = 1, 2, 3…)
• Page Size (records per page)

Formula:

OFFSET = (PageNumber - 1) * PageSize

2. SQL Implementation (Most Common)

Using OFFSET FETCH (SQL Server)

SELECT *
FROM Employees
ORDER BY Id
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

Example

Page 2, Page Size 10:

SELECT *
FROM Employees
ORDER BY Id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

3. MySQL / PostgreSQL

SELECT *
FROM employees
ORDER BY id
LIMIT @PageSize OFFSET (@PageNumber - 1) * @PageSize;

4. API Implementation (ASP.NET MVC / Web API)

Request Model

public class PaginationRequest
{
    public int PageNumber { get; set; } = 1;
    public int PageSize { get; set; } = 10;
}

API Method

public async Task<object> GetEmployees(PaginationRequest request)
{
    int skip = (request.PageNumber - 1) * request.PageSize;

    var data = await _context.Employees
        .OrderBy(x => x.Id)
        .Skip(skip)
        .Take(request.PageSize)
        .ToListAsync();

    var totalRecords = await _context.Employees.CountAsync();

    return new
    {
        Data = data,
        PageNumber = request.PageNumber,
        PageSize = request.PageSize,
        TotalRecords = totalRecords,
        TotalPages = (int)Math.Ceiling((double)totalRecords / request.PageSize)
    };
}

5. API Response Format (Best Practice)

{
  "data": [...],
  "pageNumber": 2,
  "pageSize": 10,
  "totalRecords": 95,
  "totalPages": 10
}

6. Advanced (Production-Level)

1. Add Filtering + Sorting

SELECT *
FROM Employees
WHERE DepartmentId = @DeptId
ORDER BY Salary DESC
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;

2. Keyset Pagination (High Performance)

Instead of OFFSET (slow for large data):

SELECT *
FROM Employees
WHERE Id > @LastId
ORDER BY Id
LIMIT @PageSize;

Why better?

• No full scan
• Works great for infinite scroll

7. Performance Tips

• Always use ORDER BY (mandatory for pagination)
• Add index on sorted column (e.g., Id)
• Avoid large OFFSET (slow queries)
• Prefer Keyset Pagination for big tables

8. Interview Summary

If asked:

“How do you implement pagination?”

Answer:

• Use OFFSET FETCH or LIMIT OFFSET
• Return metadata (total records, total pages)
• Optimize with indexing
• For large data → use Keyset Pagination

9. Bonus: Stored Procedure

CREATE PROCEDURE GetEmployeesPaged
    @PageNumber INT,
    @PageSize INT
AS
BEGIN
    SELECT *
    FROM Employees
    ORDER BY Id
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END