Ok, you got the budget and eventually make decision to partitioning your table, then let’s start.
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.
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 have to select the option Left Boundary or Right Boundary and then click the button that says Set Boundaries.
The Boundary column label in the grid will dynamically display <= Boundary when you select Left 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’.
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.