asked
Dynamic Partitioning
Hi all, Is it possible to do a dynamic partitioning in SQL Server? I know if my range or boundary points are of Month or year type i can achieve this through a job. But how can i do the same for range or boundary points other than Month or Year? Thanks in Advance
You would achieve the "dynamic" part of partitioning the same way for a partition on an int column as you would for a datetime column. You modify the partition boundaries so that the window of the partition is moved. Take a look at this article on [Implementing an Automatic Sliding Partition][1]. While the example is for a date column, the range can be defined on just about any column. For a table where you want to partition by an int column, you define the ranges on that instead of date. [1]:
http://msdn.microsoft.com/en-us/library/aa964122(SQL.90).aspx
I'm not sure it's necessarily what you're looking for, but you can partition on a computed column if that computed column is persisted. So, for instance, you could partition based on ID % 10 - or something like that. But, any function that you use in there would necessarily have to be deterministic - so making a function which returned a different partition number depending on when it was called (for a sliding window effect) would be a no-go.
That was just an example really. I was thinking it would be more useful for things like transaction items - where you want to pick up items from a specific transaction and the ID would be known... But certainly not a suggestion to go doing that, just an illustration of how you might use a persisted computed column...
You do not even place the partitions on different volumes to get a performance boost. You will get a good performance boost by the "partition elimination" process in SQL server. I have implemented something similar with a 200% performance boost without placing the partitions on separate volumes. Remember to apply the calculated column in your where clause to get the partition elimination to work.
Rule of thumb, avoid moving pages. You can safely: * Add a new partition boundary at the end of the table * Switch in a partition * Switch out a partition These can cause locks and will likely move pages around: * Merging * Splitting Partition elimination is a great advantage and you do not need to have the partitions sitting on different volumes. In SQL 2005, the engine can only open 1 thread per partition. In 2008 it can open multiple threads per partition hence increasing performance.
