September 2008 Blog Posts

Automating RANGE RIGHT Sliding Window Maintenance

I posted example scripts to automate RANGE LEFT sliding window maintenance in my last post.  As promised, I am sharing a RANGE RIGHT version in this post. I personally prefer a RANGE RIGHT partition function when partitioning on a data type that includes time.  RANGE RIGHT allows specification of an exact date boundary instead of the maximum date/time value needed for RANGE LEFT to keep all data for a given date in the same partition.  Another nicety with RANGE RIGHT is that same boundaries can be used in a RANGE RIGHT partition function of any date/time data type.  In contrast, the...

Automating Sliding Window Maintenance

I mentioned in my last post that partitioning provides a great way to manage a time-based sliding window.  Unfortunately, there are no out-of-the-box tools to facilitate adjusting the sliding window so I decided to share scripts I’ve developed to automate the task. Details of a time-based sliding window strategy vary slightly depending on whether the partition function is RANGE RIGHT or RANGE LEFT.   The default RANGE LEFT is pervasive so I’ll post a RANGE LEFT example here.  I’ll follow up with a RANGE RIGHT version in my next post. The stored procedure below shows how you can automate a RANGE LEFT daily...