Partitioning Database Table

Introduction


Partitioning makes large tables or indexes more manageable, because partitioning enables you to manage and access subsets of data quickly and efficiently, while maintaining the integrity of a data collection.

The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. The table or index is treated as a single logical entity when queries or updates are performed on the data. All partitions of a single index or table must reside in the same database.


Partitioned tables and indexes support all the properties and features associated with designing and querying standard tables and indexes, including constraints, defaults, identity and timestamp values, and triggers. Therefore, if you want to implement a partitioned view that is local to one server, you might want to implement a partitioned table instead.


Deciding whether to implement partitioning depends primarily on how large your table is or how large it will become, how it is being used, and how well it is performing against user queries and maintenance operations.

• Generally, a large table might be appropriate for partitioning if both of the following are true:
• The table contains, or is expected to contain, lots of data that are used in different ways.

Queries or updates against the table are not performing as intended, or maintenance costs exceed predefined maintenance periods.


For example, if a current month of data is primarily used for INSERT, UPDATE, DELETE, and MERGE operations while previous months are used primarily for SELECT queries, managing this table may be easier if it is partitioned by month. This benefit can be especially true if regular maintenance operations on the table only have to target a subset of the data. If the table is not partitioned, these operations can consume lots of resources on an entire data set. With partitioning, maintenance operations, such as index rebuilds and defragmentations, can be performed on a single month of write-only data, for example, while the read-only data is still available for online access.


When to use:


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.


Steps to create Partitioning:
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.

See how easy it is :)