[Enh]: New pagination keywords

[JerryNixon]

Existing, unchanged keywords

• $first: return the first N items, similar to SQL TOP.
• $after: return items after this cursor; not offset-based.

New keywords

• $pageSize: number of records per page
• $pageNumber: numeric page index (1-based)

Default behavior

• $pageSize defaults to the configured DAB page-size value if not specified.
• $pageNumber defaults to 1 if not specified.

Evaluation precedence

$after → $pageSize → $pageNumber → $first

They can all be used together.

Keywords not being added

I include this section of the specification because these keywords were part of previous conversations and specifications. However, we are not going to add them anymore because they are redundant.

• $take: semantically identical to $first.
• $skip: semantically equivalent to $pageSize + $pageNumber. For example, $skip=10 can be expressed as $pageSize=5 and $pageNumber=3. Skip behavior can also be achieved using $after, optionally combined with $first.

Examples in TSQL

-- Example 1: All parameters NULL (no paging applied)

DECLARE @First      int = NULL;
DECLARE @After      int = NULL;
DECLARE @PageSize   int = NULL;
DECLARE @PageNumber int = NULL;

SELECT *
FROM dbo.Actor
ORDER BY Id;

-- Example 2: Page model ($pageSize + $pageNumber)
-- DAB requests PageSize + 1 to determine NextLink

DECLARE @First      int = NULL;
DECLARE @After      int = NULL;
DECLARE @PageSize   int = 5;
DECLARE @PageNumber int = 3;

SELECT *
FROM dbo.Actor
ORDER BY Id
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT (@PageSize + 1) ROWS ONLY;

-- Example 3: $pageSize only (defaults pageNumber = 1)
-- DAB fetches PageSize + 1 for NextLink detection

DECLARE @First      int = NULL;
DECLARE @After      int = NULL;
DECLARE @PageSize   int = 5;
DECLARE @PageNumber int = NULL;

SET @PageNumber = ISNULL(@PageNumber, 1);

SELECT *
FROM dbo.Actor
ORDER BY Id
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT (@PageSize + 1) ROWS ONLY;

-- Example 4: Cursor model ($first only)
-- DAB fetches First + 1 for NextLink detection

DECLARE @First      int = 5;
DECLARE @After      int = NULL;
DECLARE @PageSize   int = NULL;
DECLARE @PageNumber int = NULL;

SELECT TOP (@First + 1) *
FROM dbo.Actor
ORDER BY Id;

-- Example 5: Cursor model ($first + $after)
-- DAB fetches First + 1 for NextLink detection

DECLARE @First      int = 5;
DECLARE @After      int = 10; -- simplified for this example
DECLARE @PageSize   int = NULL;
DECLARE @PageNumber int = NULL;

SELECT TOP (@First + 1) *
FROM dbo.Actor
WHERE Id > @After
ORDER BY Id;

-- Example 6: $after + $pageSize
-- DAB fetches PageSize + 1 for NextLink detection

DECLARE @First      int = NULL;
DECLARE @After      int = 10; -- simplified for this example
DECLARE @PageSize   int = 5;
DECLARE @PageNumber int = NULL;

SELECT TOP (@PageSize + 1) *
FROM dbo.Actor
WHERE Id > @After
ORDER BY Id;

-- Example 7: $first + $pageSize + $pageNumber
-- Page window fetches PageSize + 1
-- Final limiter applies after trimming NextLink row

DECLARE @First      int = 2;
DECLARE @After      int = NULL;
DECLARE @PageSize   int = 5;
DECLARE @PageNumber int = 3;

WITH Paged AS
(
    SELECT *
    FROM dbo.Actor
    ORDER BY Id
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT (@PageSize + 1) ROWS ONLY
)
SELECT TOP (@First)
*
FROM Paged
ORDER BY Id;

-- Example 8: ⛔ Incompatible: $after + $pageNumber

DECLARE @First      int = NULL;
DECLARE @After      int = 10; -- simplified for this example
DECLARE @PageSize   int = NULL;
DECLARE @PageNumber int = 2;

IF @After IS NOT NULL AND @PageNumber IS NOT NULL
    THROW 50000, '$after cannot be combined with $pageNumber.', 1;

-- Example 9: ⛔ Invalid: $pageNumber without $pageSize

DECLARE @First      int = NULL;
DECLARE @After      int = NULL;
DECLARE @PageSize   int = NULL;
DECLARE @PageNumber int = 2;

IF @PageNumber IS NOT NULL AND @PageSize IS NULL
    THROW 50000, '$pageNumber requires $pageSize.', 1;

-- Example 10: ⛔ Invalid negative values

DECLARE @First      int = -5;
DECLARE @After      int = NULL;
DECLARE @PageSize   int = -10;
DECLARE @PageNumber int = NULL;

IF @First IS NOT NULL AND @First <= 0
    THROW 50000, '$first must be greater than zero.', 1;

IF @PageSize IS NOT NULL AND @PageSize <= 0
    THROW 50000, '$pageSize must be greater than zero.', 1;