Configuring OFFSET‑FETCH pagination in Azure SQL Database when Row‑Level Security is enabled

When using Azure SQL Database to paginate a large result set with the OFFSET‑FETCH clause, you may also have Row‑Level Security (RLS) policies that filter rows before the offset is applied. The goal is to retrieve a predictable page size (e.g., 20 rows) regardless of how many rows are hidden by RLS, while keeping the query plan efficient across different offset values. It is unclear whether the OFFSET value must account for the variable number of rows removed by RLS, whether OPTION (RECOMPILE) is required to prevent plan caching problems when the offset changes, and what practical limits exist for the offset before performance degrades under RLS.

Does the OFFSET value need to be increased to compensate for rows filtered by RLS to maintain a fixed page size? Should OPTION (RECOMPILE) be added to avoid plan reuse issues when the offset varies across executions? Is there a recommended maximum offset value beyond which keyset pagination becomes preferable when RLS is active?