How does a sliding window pattern work for automatically archiving old data using partitioning?

Intermediate

Answer

A sliding window pattern involves regularly adding a new empty partition for upcoming data and switching out the oldest partition, moving its data to an archive table almost instantly since a partition switch is a metadata only operation, letting you efficiently manage a rolling window of recent data without slow, resource intensive delete operations.

ALTER TABLE Sales SWITCH PARTITION 1 TO SalesArchive PARTITION 1;

Real-world example A company keeps only the most recent two years of sales data in their main operational table, using a sliding window pattern to instantly switch out the oldest partition into an archive table each time a new year begins.

Common follow-ups: Why is a partition switch so much faster than a traditional DELETE statement?;What requirements must be met for a partition switch to succeed?

Backup & Recovery;Query Optimization & Plans

All 7 Partitioning questions