Question

You manage an Azure SQL Managed Instance hosting a Sales.Orders table that grows by roughly 20 million rows per month. The business requires that orders older than 36 months be removed from the active table each month with minimal locking and I/O, while allowing the archived data to be retained separately. You want to implement a repeatable monthly maintenance process that avoids large DELETE operations. Which approach should you implement?

Accepted Answer

Create a partitioned table using a monthly partition function and scheme, then use a sliding-window strategy with SWITCH PARTITION to move the oldest partition to a staging table each month.

Answer

Enable page compression on the table and run a monthly DELETE statement in batches with the ROWLOCK hint to remove aged rows.

Answer

Configure a temporal (system-versioned) table so old rows automatically move to the history table after 36 months.

Answer

Shard the Orders table across multiple databases by month and drop the oldest database each month.