Summary

Fixes a pagination bug introduced in commit 6dd540c that caused custom SearchParameters to become "unsupported" after pod restarts.

Problem

The subquery wrapper introduced in #5297 placed TOP inside the inner SELECT without ORDER BY:

-- BEFORE (buggy)
SELECT * FROM (
  SELECT DISTINCT TOP (@p0) r.ResourceTypeId, ...
  FROM dbo.Resource r
  WHERE ResourceTypeId = 123 AND IsHistory = 0
  -- No ORDER BY here!
) AS t ORDER BY t.ResourceTypeId ASC, t.ResourceSurrogateId ASC

This caused SQL Server to return arbitrary rows in clustered index order before the outer ORDER BY reordered them. When pagination continued with ResourceSurrogateId > lastValue, rows with high GUIDs but low surrogate IDs were skipped.

Solution

Move TOP to the outer SELECT so it applies after ORDER BY:

-- AFTER (fixed)
SELECT TOP (@p0) * FROM (
  SELECT DISTINCT r.ResourceTypeId, ...
  FROM dbo.Resource r
  WHERE ResourceTypeId = 123 AND IsHistory = 0
) AS t ORDER BY t.ResourceTypeId ASC, t.ResourceSurrogateId ASC

Impact

• ~50 FHIR services affected with 7,000-12,000 errors/day since Dec 31, 2025
• Customer example: Genome loaded 36 SearchParameters on Jan 21, only 24 on Jan 24 after pod restart
• Affects any account with custom SearchParameters where pagination order differs from clustered index order

Testing

• Verified locally that generated SQL now has TOP on outer query
• Confirmed correct pagination behavior with continuation tokens

Related

• Fixes ICM 2601260050003542
• Regression from Fix infinite loop with multiple includes #5297 (commit 6dd540c)

AB#182301