THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Partition Wizard Makes it Simple

The partitioned table was one of the great new features of SQL Server 2005. We could now manage data based on some range data, usually a date value, and keep the different ranges in separate physical files, and use rolling range scripts to move old data out of the table and create new partitions for new data and incorporate those new ranges into the table, and didn't have to change the application to keep it working properly.

The feature is cool, setting it up turned out to be a pretty large task, and when setting up a partitioned table over two years history with millions of rows of data, it took hours to plan and set up.

SQL Server 2008 introduces a wizard that makes the creation of the partition function and partition wizard fairly easy to understand and very simple to implement.

By right-clicking on the target table, a menu option called Storage appears, and you can then select Create Partition. Select the column to be used as the partitioning column, specify names for your partition function and scheme, then the Map Partition dialog is displayed. You can specify RANGE RIGHT or RANGE LEFT using the right boundary or left boundary radio buttons at the top of the dialog. (Boundary makes much more sense than RANGE, doesn't it?) Also, when the left boundary button is selected, the header of the second column in the filegroups grid indicates "<=Boundary", and when the right boundary button is selected, the header indicates "<Boundary", so it helps  you understand the decisions you're making.

Now, let's say you've got two years of data you want to partition, with a partition for each month. Just click the Set Boundaries button, enter the start and end dates and the range you want to partition on (Monthly, Quarterly, Yearly, etc.) and the wizard creates those ranges for you automatically. Now, all you have to do is assign each of the date ranges calculated to the partitions you'll have to have already set up in the database. (You do have to set up the filegroups and files before starting the wizard - it can't do that for you.) You have to also include a filegroup to catch the data falling outside your defined range, so if you've set up 24 ranges for two years of data, you'll need 25 filegroups defined.

The partitioned table is a great feature you should be using to manage data more effectively. With SQL Server 2008 it'll be even easier to implement.

Have fun!


Published Monday, July 28, 2008 9:44 AM by AllenMWhite


No Comments
New Comments to this post are disabled

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog


Privacy Statement