SQL Server

SQL Server supports multiple approaches for pagination:

1. Nested TOP Queries (common in older versions):

   SELECT TOP (@pagesize) *
   FROM (
       SELECT TOP ((@pageindex + 1) * @pagesize) *
       FROM tbl
       ORDER BY sortcolumn ASC
   ) AS sub
   ORDER BY sortcolumn DESC;
   

2. ROW_NUMBER() with CTE (requires SQL Server 2005 SP2 or later):

   WITH PaginatedData AS (
       SELECT *, ROW_NUMBER() OVER (ORDER BY sortcolumn) AS rn
       FROM tbl
   )
   SELECT *
   FROM PaginatedData
   WHERE rn > @pagesize * @pageindex
     AND rn <= @pagesize * (@pageindex + 1);
   

3. SET ROWCOUNT (deprecated as of SQL Server 2012):

   DECLARE @LastValue INT;
   SET ROWCOUNT @startrow;
   SELECT @LastValue = sortcolumn FROM tbl ORDER BY sortcolumn;
   SET ROWCOUNT @pagesize;
   SELECT *
   FROM tbl
   WHERE sortcolumn >= @LastValue
   ORDER BY sortcolumn;
   

MySQL

MySQL uses the LIMIT clause for efficient offset-based pagination:

SELECT *
FROM tbl
ORDER BY sortcolumn
LIMIT @offset, @pagesize;

Here, @offset is typically calculated as @pageindex * @pagesize.

Oracle

Oracle traditionally relies on ROWNUM, but it must be used carefully due to its evaluation timing:

A correct pattern involves wrapping the ordered query in a subquery:

SELECT *
FROM (
    SELECT rownum AS rn, t.*
    FROM (
        SELECT *
        FROM tbl
        WHERE filters
        ORDER BY sortcolumn
    ) t
    WHERE rownum <= (@pageindex + 1) * @pagesize
)
WHERE rn > @pageindex * @pagesize;

Using MINUS with two ROWNUM queries—as sometimes suggested—can produce inconsistent results because each subquery independently applies ordering and filtering, leading to non-deterministic output. Similarly, reusing a CTE with ROWNUM in Oracle does not work as expected because ROWNUM is asisgned before the final projection, and CTEs may be materialized different than assumed.

The reliable method remains the double-nested subquery approach that enforces ordernig first, then applies ROWNUM bounds.