Use this hidden partitioning wizard...

SQL Server

In a previous blog entry I talked about the Sliding Window partitioning scenario. I even tried to involve Gwyneth Paltrow in a SQL Server Blog which is no mean feat. (When you teach classes over the internet you have to think of original ways of gaining students' attention). Well, there’s a cool wizard that’s a hidden gem in SSMS 2008 which may help you with data partitioning…

In my last blog entry, I talked about the Data Compression wizard that helps evaluate if data compression will benefit a particular table. The wizard was accessed by using SSMS 2008 Object Explorer and right-clicking the table name and choosing Storage from the context sensitive menu. Well, there are other options there, namely Create Partition and Manage Partition.

The Create Partition wizard will generate scripts for you to create a partition function, a partition scheme and create the table using that partitioning scheme based on a single column. It will also align the indexes on the same partitioning scheme. The wizard additionally helps set up an archive table that receives old partitions from the “live” table.

The Manage Partition wizard will generate scripts that perform the sliding window scenario where the oldest partition is “switched” to the archive table instantly using meta data changes. It will also set up the “merge” and “split” to create the new partition to accept the new period data.

Microsoft provides some good samples based around the sliding window scenario (look for sliding window scenario in Books Online) but these wizards help us to apply that scenario to our own data tables. It’s just a tad bit hidden. Maybe that makes us appreciate it more.

Cheers

Brian

 Recent Posts:

Manage your Data Compression with SSMS

 Partition that Cube!

Logins and Users - SQL Server security fundamentals

SSMS Debug 2008 - Comments please?

• Microsoft
• data partitioning
• partitioning
• SQL Server
• SQL Server 2008
• wizard