A public-health lab results table must speed purge of aged rows by sample_date. Which design should the team apply?
Select an answer to reveal the explanation.
Short Explanation
Partitioning by sample_date is like storing lab boxes on shelves labeled by month—when retention expires, you remove a shelf instead of digging through every vial. Public-health purge gets faster and safer that way.
Full Explanation
Public-health lab results accumulate quickly and must be purged or archived by age based on sample_date. Partitioning the table and its indexes by sample_date ranges aligns storage with lifecycle boundaries so aged partitions can be truncated or switched out far more efficiently than row-by-row deletes across an unpartitioned heap of civic lab history. That design is the standard Azure SQL approach for date-driven retention at scale. Stuffing all results into one unpartitioned nvarchar(max) JSON document destroys relational querying and offers no partition-level purge. Disabling partitioning and relying on unconstrained lock-heavy deletes makes retention windows operationally painful and blocking. A nonclustered index on sample_date may speed filters but does not remove historical data by partition; without a purge strategy the table still grows indefinitely. Caveat: choose a partition function and scheme that match retention (for example, monthly) and keep indexes aligned; misaligned indexes undermine partition switching and truncate operations. Operational check: after partitioning, switch or truncate an aged sample_date partition in a non-production copy and confirm only that date range disappears while recent partitions remain queryable.