Tuesday 13 May 2014

Automating Sliding Partition Windows - Part 3

It's been a long while since I've worked on this - and believe me I want to as every month I'm spending too much time manually sliding my various partitions! Which is very prone to mistakes.

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:
http://blogs.msdn.com/b/menzos/archive/2008/06/30/table-partitioning-sliding-window-case.aspx