---
title: "How to implement Pagination in API with SQL?"  
description: "How to implement Pagination in API with SQL?"  
author: "Ravi Vishwakarma"  
published: 2026-03-16  
updated: 2026-04-13  
canonical: https://answers.mindstick.com/qa/116423/how-to-implement-pagination-in-api-with-sql  
category: "asp.net"  
tags: ["asp.net"]  
reading_time: 3 minutes  

---

# How to implement Pagination in API with SQL?

## Answers

### Answer by Anubhav Sharma

Pagination in APIs is essential for [performance and scalability](https://answers.mindstick.com/qa/113839/how-do-programming-languages-influence-the-performance-and-scalability-of-applications)—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:

```plaintext
OFFSET = (PageNumber - 1) * PageSize
```

## 2. SQL Implementation (Most Common)

## Using `OFFSET FETCH` (SQL Server)

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

## Example

Page 2, Page Size 10:

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

## 3. MySQL / PostgreSQL

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

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

### Request Model

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

### API Method

```cs
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)

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

## 6. Advanced (Production-Level)

## 1. Add Filtering + Sorting

```plaintext
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):

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

### Why better?

- No full scan
- Works great for [infinite scroll](https://answers.mindstick.com/qa/116260/infinite-scroll-stops-loading-new-tweets)

## 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](https://www.mindstick.com/forum/158259/how-do-you-implement-pagination-for-a-list-of-items-using-jquery)?”

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

```plaintext
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
```


---

Original Source: https://answers.mindstick.com/qa/116423/how-to-implement-pagination-in-api-with-sql

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.