FireDAC and DBMS Interoperability: Server-Side Pagination Consistency

Short answer

No. FireDAC has no built-in layer that translates one pagination statement into dialect-specific LIMIT/OFFSET, ROWNUM, or ROW_NUMBER() forms. The SQL you place in TFDQuery.SQL is sent to the driver essentially as written. The nearest supported abstraction is macro substitution plus runtime branching on TFDConnection.RDBMSKind — and you still author each dialect fragment yourself.

Confirmed behavior versus version-sensitive details

Well-established behavior:

• FireDAC does not rewrite pagination SQL into a dialect-specific form.
• FetchOptions bounds the fetch, not the statement. RecsMax is the page size and RecsSkip is the number of rows to skip; both act after the server has produced the result set.
• FetchOptions.RecordCount is not a page-size setting — it governs whether an exact row count is produced. Treat it as a separate concern from paging.
• Macros (TFDQuery.Macros, !Name placeholders) expand before the statement reaches the driver, so one SQL text can carry dialect fragments.
• RDBMSKind identifies the driver family, not the server version.

Version-sensitive and worth confirming locally: exact property names, defaults, and enum spellings differ between Delphi releases and driver builds. Verify them in the IDE property inspector rather than trusting any snippet, including the ones below.

Steps for this case

1. Separate the two mechanisms. Use SQL clauses for server-side bounding; use RecsMax only as a client-side safety cap. Never combine a SQL OFFSET with a non-zero RecsSkip, or rows are skipped twice.
2. Always add a deterministic ORDER BY over a unique key. Without it, page contents are undefined even on a single DBMS.
3. Write one statement with macro placeholders:

   SELECT t.Id, t.Name
   FROM MyTable t
   ORDER BY t.Id
   !LIMIT
   !OFFSET

4. Branch at runtime, gating on server version as well as driver family:

   case FDConnection.RDBMSKind of
     mkPostgreSQL, mkMySQL, mkSQLite:
       begin
         FDQuery.Macros.MacroByName('LIMIT').Value :=
           'LIMIT ' + IntToStr(PageSize);
         FDQuery.Macros.MacroByName('OFFSET').Value :=
           'OFFSET ' + IntToStr(PageOffset);
       end;
     mkMSSQL, mkOracle, mkFirebird:
       begin
         FDQuery.Macros.MacroByName('LIMIT').Value :=
           'OFFSET ' + IntToStr(PageOffset) +
           ' ROWS FETCH NEXT ' + IntToStr(PageSize) + ' ROWS ONLY';
         FDQuery.Macros.MacroByName('OFFSET').Value := '';
       end;
   end;

   Confirm the enum names in your build. The second branch assumes OFFSET/FETCH support (SQL Server 2012+, Oracle 12c+, Firebird 3+). For Oracle pre-12c, replace the whole SQL text with a ROW_NUMBER() subquery rather than trying to express it as a macro fragment.

5. Keep the fetch mode on demand and make sure no option is set to fetch the entire result set, which would silently negate the bounding.

Why page contents still shift

Even with correct dialect SQL, OFFSET pagination re-evaluates the result set on every request. Concurrent inserts or deletes move rows between pages, producing duplicates or gaps. If your pages must be stable under concurrent writes, the recommendation changes to keyset (seek) pagination on the ordered unique key; OFFSET remains acceptable for read-mostly or snapshot-consistent workloads.

Verification

Attach a FireDAC monitor link to the connection and enable monitoring for SQL, then capture the exact statement sent to each DBMS. Confirm that no rewriting occurs and that the expected fragment was substituted. Run the same logical page against each target DBMS with a fixed ORDER BY and compare returned key sets and ordering. Then insert and delete rows between two page fetches and re-read both pages to measure duplicate and omission behavior.

One detail that changes the recommendation

Do the pages need to stay stable while other sessions write to the table? If yes, plan for keyset pagination and treat the dialect macro layer as a fallback; if no, the macro approach above is sufficient.