Implementing Server‑Side Pagination in Firebird with FIRST and SKIP

The Problem: Client‑Side Memory Exhaustion

Fetching an entire dataset of thousands of records to a client application just to display ten rows on a screen can quickly exhaust memory and increase network latency. Firebird solves this by allowing the server to return only the rows that the client actually needs, using the FIRST and SKIP clauses.

How FIRST and SKIP Work

The FIRST N clause limits the result set to the first N rows that the engine has produced after applying any ORDER BY logic. The SKIP M clause tells the engine to discard the first M rows of the sorted result set before starting to return rows. In practice, the engine scans and discards M rows, then collects N rows and sends them to the client.

Deterministic Ordering is Mandatory

Because relational databases do not guarantee a default row order, omitting ORDER BY can lead to duplicate or missing records between pages. Always include a unique column (typically a primary key) in the sort so that the same page always returns the same rows.

Worked Configuration Example

Assume a table named INVOICES with a primary key INV_ID. The following queries illustrate a simple pagination scheme that shows 20 rows per page.

Page 1 (Rows 1‑20)

-- Run in Firebird SQL Editor or from an application
-- Required permissions: SELECT on INVOICES
SELECT FIRST 20
       INV_ID, INV_DATE, CUSTOMER_NAME
FROM INVOICES
ORDER BY INV_DATE DESC, INV_ID ASC;

Page 2 (Rows 21‑40)

-- Run in Firebird SQL Editor or from an application
-- Required permissions: SELECT on INVOICES
SELECT FIRST 20 SKIP 20
       INV_ID, INV_DATE, CUSTOMER_NAME
FROM INVOICES
ORDER BY INV_DATE DESC, INV_ID ASC;

Check that the first query returns exactly 20 rows and that the second query returns the next 20 rows—no overlap in INV_ID values.

Performance Limits and Common Pitfalls

Offset Penalty

Firebird must still scan the skipped rows. A query like SKIP 10 000 forces the engine to evaluate 10,000 rows before it can start returning data, which can be expensive for large offsets. For very deep pagination, consider alternatives such as key‑set pagination.

Index Alignment

The columns used in the ORDER BY clause should be indexed. If the engine must perform a full table sort in memory, the benefit of server‑side pagination is largely lost. Adding a covering index on the sort columns can keep the scan fast.

Common Implementation Mistakes

Mistake	Consequence	Correction
Omitting ORDER BY	Inconsistent pages; duplicate records.	Always include a unique column (e.g., primary key) in the sort.
Using dynamic SKIP values without validation	SQL injection or negative offset errors.	Cast input to integer and ensure value is ≥ 0.
Applying FIRST/SKIP to views without indexes	Slow response times on large views.	Ensure underlying tables have appropriate indexes.

How to Verify Your Pagination

1. Boundary Test: Run SELECT FIRST 1 SKIP 0 INV_ID FROM INVOICES ORDER BY INV_ID and confirm exactly one row is returned.
2. Offset Test: Run SELECT FIRST 1 SKIP 1 INV_ID FROM INVOICES ORDER BY INV_ID and confirm the returned row is the second record in the sorted sequence.
3. Consistency Test: Execute SELECT FIRST 10 SKIP 10 INV_ID FROM INVOICES ORDER BY INV_ID twice. If the results differ, your ORDER BY clause is not deterministic.