In the meantime I found a really good post describing an automated sliding partition window that achieves something a little different. It works on the following premise:
- 7 day partitions that, at the end of each week, are merged into
- 5 weekly partitions that are then merged into
- 6 monthly partitions that are finally merged into
- 1 large archive partition
I'm looking at implementing something like this in my company's OLAP database and combined with SQL Server 2012's columnstore indexes I am hoping to get some great performance improvement!
Here's the post: