Kendra Little explains that there are a couple of models available for partitioned table management:
I recently received a terrific question about table partitioning:
I’m writing up a proposal for my company to start partitioning a 2.5TB table. The idea/need is to control the size of the table while saving the old data. The old data will be moved to an archive database on a different server where the BI guys work with it.
In none of the videos articles I’ve seen is the explanation of how the rolling partition works on a long term daily basis.
- Are the partitions reused, like in a ROUND ROBIN fashion?
- Or, do you add new partitions each day with new filegroups, drop the oldest partition off – this would be FIFO?
Lots of folks assume the answer here is always #2, simply because there’s a bunch of sample code out there for it.
But option #1 can be simpler to manage when it fits your data retention technique!
Click through to learn more about reusable partitioning.