SQL Server partitioning (II) — GUI to setup table partitioning

Ok, you got the budget and eventually make decision to partitioning your table, then let’s start.

Partitioning Steps

You should partition your tables that has accumulated a lot of data over the time and you find that the performance is slowing down. It would not be a very good idea to partition a table it has just a couple of hundred rows for example : Product Category table. You could partition your tables that contains a lot of transactions like Purchase Order tables, Sales Order tables, General Ledger Tables etc.

Step 1:
Open the SQL Server 2008 management studio and select the appropriate table that needs to be partitioned. Right click on the table and in the context menu select Storage >> Create Partition.

Step 2:
I am using the [Purchasing.WorkOrder] table in the ‘AdventureWorks’ sample database. When you select the above option a wizard dialog box appears, press the next button once.

You will see a dialog box with the columns available for partition with data type, length etc. Select the column on which you would like to create a partition and press next.

Step 3:
Provide the name of the partition function and press Next.

Step 4:
Provide the name of the partition scheme and press next.

Step 5:

You have to select the option Left Boundary or Right Boundary and then click the button that says Set Boundaries.

Left boundary
The Boundary column label in the grid will dynamically display <= Boundary when you select Left boundary.

Right boundary
Select to include range values up to the specified value in the Boundary column for each filegroup selected. The specified value will be the starting value for the range values of the filegroup on the next row. The Boundary column label in the grid will dynamically display < Boundary when you select Right boundary.

A small dialog box appears with the caption “Set Boundary Values”. Select the starting and ending date and select the Date Range from Monthly, Yearly, Quarterly, Half-Yearly , Daily and press OK. I am using End Date field for the partition in this demo.

The above option will create the partition ranges for you. Select the appropriate File Group for each partition in the grid and then click on the ‘Estimated Storage’ button which will give you the details of the required space. Once you are happy with the results, please press ‘Next’.

Step 6:
After pressing the ‘Next’ button you will get the option to create a script or to run it immediately or to schedule it for a later time. Depending on your requirements you can select the option and press the finish button.

Done……

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s