How do you implement pagination in SQL?

Pagination in SQL is used to fetch a specific subset of records (like page 1, page 2, etc.) instead of retrieving the entire result set at once. It’s mainly used in applications that display large amounts of data — for example, showing 10 or 20 rows per page in a web or reporting interface.

The implementation depends slightly on the SQL dialect, but the logic remains the same — use OFFSET and FETCH (or LIMIT) to skip and take only the required rows.

For example, in SQL Server:

SELECT EmployeeID, FirstName, LastName, Department
FROM Employees
ORDER BY EmployeeID
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

Here,

• OFFSET 0 means skip 0 rows (start from the first record),
• FETCH NEXT 10 ROWS ONLY means return only 10 records.
  To get the next page, I just change OFFSET to (page_number - 1) * page_size.

In MySQL or PostgreSQL, the syntax is slightly different:

SELECT EmployeeID, FirstName, LastName, Department
FROM Employees
ORDER BY EmployeeID
LIMIT 10 OFFSET 0;

Or simply LIMIT 10, 0 depending on the database.

I applied this concept in a Power BI report where data was fetched from SQL dynamically — instead of loading all rows, I paginated the query to improve performance and reduce memory usage.

A challenge I faced was performance degradation when using large OFFSET values — for example, if you skip thousands of rows, the database still scans them internally before returning the next page.
To overcome this, I sometimes use keyset pagination, where I filter based on the last seen ID instead of OFFSET. For example:

SELECT *
FROM Employees
WHERE EmployeeID > @LastSeenID
ORDER BY EmployeeID
LIMIT 10;

This approach is faster for large datasets.

In short, pagination helps manage and display large data efficiently, and the best approach depends on dataset size and performance needs.