Pagination Behavior with Row-Level Security
In Azure SQL Database, the OFFSET-FETCH clause is applied after the Row-Level Security (RLS) filter predicate has been evaluated. Because the security predicate acts as an implicit WHERE clause, the engine filters out unauthorized rows before determining which rows to skip (OFFSET) and which to return (FETCH).
Addressing Your Specific Constraints
- Offset Compensation: No, you do not need to increase the
OFFSET value to compensate for filtered rows. Since RLS removes rows before the offset is calculated, an OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY will always return the first 20 rows the user is actually authorized to see.
- Plan Reuse and OPTION (RECOMPILE): While
OFFSET-FETCH can lead to parameter sniffing issues if the distribution of data varies wildly across pages, OPTION (RECOMPILE) is generally not required specifically because of RLS. However, if you notice significant performance degradation as the offset increases (deep paging), recompilation may help the optimizer choose a better plan for that specific offset value.
- Maximum Offset and Keyset Pagination: There is no hard-coded limit, but performance degrades linearly as the offset increases because the engine must still scan and discard the preceding rows. When RLS is active, the engine must evaluate the security predicate for every row scanned. Once the offset reaches a point where the "cost to skip" outweighs the "cost to fetch," you should switch to keyset pagination (using
WHERE ID > @last_seen_id).
Implementation Steps
- Ensure Predicate Application: Verify that your RLS predicate is an inline table-valued function. Multi-statement functions are not supported for RLS and will cause errors.
- Avoid Bypassing Filters: Ensure you are querying the table directly or through a schema-bound view. Non-schema-bound views or certain CTE structures can occasionally interfere with how predicates are applied during pagination.
- Apply Order: Always include a deterministic
ORDER BY clause. Without it, OFFSET-FETCH is non-deterministic, and RLS filtering may result in inconsistent pages across requests.
Verification Workflow
To confirm that RLS is filtering rows before the pagination occurs, run the following check as a restricted user:
-- Verify that the page size is consistent regardless of hidden rows
SELECT * FROM dbo.YourSecureTable
ORDER BY YourSortColumn
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;
Inspect the Execution Plan. You should see a Filter operator (the RLS predicate) appearing before the Fetch operator in the plan tree. If the Filter appears after the Fetch, your security is not being applied correctly to the result set.
Diagnostic Detail Needed: Are you accessing the RLS-protected table through a View or a Common Table Expression (CTE)? This may change the recommendation regarding how the optimizer handles the predicate.
azuresqldatabase