Pagination for Blazor Boards

Status: Reviewed (deep-dive v1)
Stack note: Alert/inventory grids must page in usp_* — don’t return the estate in one result. Prefer keyset over deep OFFSET/FETCH.
Pairs with: SQL-12, SQL-16 (order/index), SQL-24 (plans per page size).
Goal: Stable, index-friendly paging for boards.

Quick chooser

Need	Prefer
Next/prev on live boards	Keyset (`WHERE (SortCol, Id) > (@lastSort, @lastId)`)
Jump to page 50 of 500	`OFFSET/FETCH` — OK early pages; costly deep
Stable sort with ties	Unique tie-breaker (always include PK)
Total count badge	Separate cheap `COUNT` or estimate — not every page

Keyset (seek-friendly)

CREATE OR ALTER PROC dbo.usp_Alert_GetOpenBoardPage
  @AfterFirstSeen datetime2(0) = NULL,
  @AfterAlertId bigint = NULL,
  @Take int = 50
AS
BEGIN
  SET NOCOUNT ON;
  IF @Take < 1 OR @Take > 200 SET @Take = 50;

  SELECT TOP (@Take)
    a.AlertId, a.Title, a.SeverityCode, a.Status, a.FirstSeenAt
  FROM dbo.Alert a
  WHERE a.Status IN (N'Open', N'Acked')
    AND (
      @AfterAlertId IS NULL
      OR a.FirstSeenAt > @AfterFirstSeen
      OR (a.FirstSeenAt = @AfterFirstSeen AND a.AlertId > @AfterAlertId)
    )
  ORDER BY a.FirstSeenAt, a.AlertId;
END;

Blazor keeps the last row’s (FirstSeenAt, AlertId) as the cursor.

Index: (Status, FirstSeenAt, AlertId) or filtered active IX (SQL-16/29).

OFFSET/FETCH (simple, deep-page tax)

ORDER BY a.FirstSeenAt, a.AlertId
OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;

Fine for admin pages; avoid @Skip = 100000 on hot path.

Habits

[ ] Cap @Take (e.g. 100–200)
[ ] Same ORDER BY in proc and UI
[ ] Don’t SELECT *
[ ] Count: cached or async — not blocking the page read

Done when

[ ] Hot boards use keyset (or documented OFFSET limit)
[ ] Supporting IX matches ORDER BY
[ ] This note linked from Knowledge Base list

SQL Dude — SQL-25 Pagination for Blazor Boards v1