Sliding Partition Window Concept
Before we continue, let’s remind ourselves of what the
Sliding Partition Window is and what it gets us. The whole point of the sliding
window is to create a sort of data conveyor belt. New data comes into the
production table and is placed in the empty partitions (and files where
relevant) and the old data is “switched” or moved off to a staging table for
further processing and, crucially, removal. In this way the table slides
forward along its partitions but remains essentially the same size. There will
always be a set number of partitions worth of data in your production table
keeping it lean and fit. In addition, once you’ve moved the data from the
staging tables to an archive or reporting database the physical files can be
removed and your production database can be maintained at a manageable, near
constant, size.