Does FireDAC TFDQuery apply Limit and Offset server-side or client-side?

Pagination Behavior in TFDQuery

FireDAC does silently fall back to client-side pagination when the connected database server or the specific driver version does not support native LIMIT and OFFSET (or FETCH) syntax. If the driver cannot translate the TFDQuery.Limit and TFDQuery.Offset properties into a native SQL clause, FireDAC retrieves the full result set from the server and applies the bounds locally within the client application.

RecordCount and Result Set Behavior

The behavior of RecordCount differs significantly depending on whether the pagination was pushed to the server:

• Client-Side Fallback: RecordCount reflects the complete, unfiltered result set. While you only iterate through the bounded rows, the entire dataset has been downloaded to the client memory.
• Server-Side Execution: RecordCount typically reflects only the bounded count (or returns -1 depending on the driver's cursor implementation), as the server only transmitted the requested slice of data.

Guaranteed Server-Side Combinations

Server-side execution is generally guaranteed when using modern RAD Studio versions (10.4 Sydney and later) paired with servers that support native pagination syntax. Common supported combinations include:

Database	Minimum Version for Native Support	SQL Syntax Used
PostgreSQL / MySQL / SQLite	Most versions	LIMIT / OFFSET
SQL Server	2012 (11.x) or later	OFFSET / FETCH NEXT
Oracle	12c (12.1) or later	OFFSET / FETCH NEXT
Firebird	3.0 or later	ROWS / FETCH

Verification Steps

Because the fallback is silent, you must verify the actual SQL being executed. Use these steps to confirm server-side pagination:

1. Enable SQL Monitoring: Deploy a TFDMoniRemoteClientLink or TFDMoniFlatFileClientLink. This allows you to inspect the raw SQL string sent to the server. If you do not see LIMIT, OFFSET, or FETCH in the monitor, pagination is happening on the client.
2. Check Driver Capabilities: At runtime, you can query the connection to see if the driver reports pagination support via FDConnection.GetInfo.
3. Memory Profile: Monitor the application's memory usage when opening a query against a million-row table with a small Limit. A spike in memory indicates a client-side fallback.

Diagnostic Detail Needed: To provide a more specific recommendation, please specify the exact version of your database server (e.g., SQL Server 2008 vs 2016) and the RAD Studio version you are using.