Partitioning of Table in SQL Server

If you have very large database then you can use Partition on those tables to optimise the performance of your database. The concept of partition was introduced with SQL Server 2005. The benefit is that partitioining allows you to spread data into different physical disks,leveraging the concurrent performance of those disks to optimize query performance.

Partitioning a SQL Server database table is 3 steps process :

1. Creating Partition function

2. Create Partition Scheme

3. Partitioning a Table

Let's have a closer look at each of these steps.

Step 1 : Creating a Partition Function.

The partition function defines how you want SQL Server to partition the data.Here you need to define boundaries of each partition.For example suppose we have a Salesorderdetail table that contains information of sales order , identified by unique salesorderID and holding records ranging from 1 to 400 .We decided to partition the table into four equally spaced partition.

 

 Following is the syntax to create Partition Function -

CREATE PARTITION FUNCTION SALES_PARTFUNC (int) As RANGE RIGHT FOR VALUES ( 100 , 200 , 300 )

These boundaries define four partitions.The first contains all values less than 100. The Second contains values between 100 and 199. The third contains vaules between 200 and 299. All the values greater than or equal to 300 go in the fourth partition.

I used the "RANGE RIGHT" clause in this example. This indicates that the boundary value itself should go in the partition on the right side. Alternatively, if I had used "RANGE LEFT", the first partition would have included all values less than or equal to 100; the second partition would have included values between 101 and 200 and so on.

Step 2 : Create Partition Scheme

Once you have a partition function describing how you want to split your data, you need to create a partition scheme defining where you want to partition it.

Creating partition scheme will link partitions function to filegroups for eg. If I created four filegroups named FG_example1 to FG_example4 then following statement will create the partition scheme.

CREATE PARTITION SCHEME RolSALES_partscheme

AS PARTITION RolSALES_PARTFUNC

TO (FG_example1, FG_example2, FG_example3, FG_example4)

Did you notice that we now link a partition function to the partition scheme, but we still haven’t linked the partition scheme to any specific database table. We could use this partition scheme on any number of database tables.That’s where the power of reusability comes into picture.

Step 3: Partitioning a Table

After defining a partition scheme, you’re now ready to create a partitioned table. This is the simplest step of the process. You simply add an "ON" clause to the table creation statement specifying the partition scheme and the table column to which it applies. You don’t need to specify the partition function because the partition scheme already identifies it.

For example, if you wanted to create a customer table using our partition scheme, you would use the following Transact-SQL statement:

CREATE TABLE SalesOrderUpdate (ProductName nvarchar(40), ProductID nvarchar(40), SalesOrderCode int)

ON RolSALES_partscheme (SalesOrderCode)

 

Once tacit this is very useful concept of SQL Server :)