Archive

Posts Tagged ‘horizontal partitioning’

SQL Server – Horizontally partitioning a SQL Server Database Table

July 22, 2011 6 comments

Partitioning was introduced in SQL Server 2005, it allows you to different database files, which can be located on different disks to improve performance. In earlier versions this was achieved by creating separate tables on different file groups of database, and then creating a view by UNIONing these tables to make it look like a single table.

First, let us create a Test database for partitioning with more than one file groups. I have created [PartitionDB] with two file groups – [PRIMARY] and [FileGroup2], both of these groups contain a data file:

CREATE DATABASE [PartitionDB] ON

PRIMARY

      (     NAME = N'PartitionDB',

            FILENAME = N'C:\PartitionDB\PartitionDB.mdf' ,

            SIZE = 3072KB , FILEGROWTH = 1024KB

      ),

FILEGROUP [FileGroup2]

      (     NAME = N'PartitionDB_2',

            FILENAME = N'C:\PartitionDB\PartitionDB_2.ndf' ,

            SIZE = 3072KB , FILEGROWTH = 1024KB

      )

LOG ON

      (     NAME = N'PartitionDB_log',

            FILENAME = N'C:\PartitionDB\PartitionDB_log.ldf' ,

            SIZE = 9216KB , FILEGROWTH = 10%

      )

image

Note: I have created both files on the same disk here, it is recommended to create files on separate disks for optimal performance.

Now we have a database to work with, let’s start with table partitioning, Partitioning a Table involves three steps:

 

Step 1: Creating a Partition Function – This is the first step in creating a partitioned table, a partition function tells SQL Server “how” to split the data. A partition function is not related to any particular table, it is a “technique” that SQL Server will use to split the data rows.

A partition function defines the range of values for a partition. For example, customer order data can be partitioned according to order date. I.e. data for current year will be in [PRIMARY] file group, while older data will be in [FileGroup2] file group. To achieve this we can create partition function as:

— Step 1. Creating a Partition Function

 

CREATE PARTITION FUNCTION ordersPartFunc (DATE)

AS RANGE LEFT

FOR VALUES ('2010-01-01')

The VALUES(<list of values>) define a list of boundary values for each partition, By specifying VALUES (2010) we have created two partitions.

1. For Values < 2010 and,
2. For Values > 2010

RANGE defines to which partition the boundary values will go. It can either be specified as RANGE LEFT or RANGE RIGHT, depending on this the placement of boundary values is decided. Here, we have used RANGE LEFT that means ‘2010’ will go to LEFT partition, so the partitions will contain below data:

1. Values <= 2010 and
2. Values > 2010

 

Step 2: Creating a Partition Scheme – Once the partition function is created, SQL Server knows “how” to split data, but it does not know where to put this partitioned data, this is defined by partition scheme. Partition scheme need to be linked to a partition function, to specify where each partition will be stored. We can create a partition scheme linked to ordersPartFunc as:

— Step 2. Creating a Partition Scheme

 

CREATE PARTITION SCHEME ordersPartScheme

AS PARTITION ordersPartFunc

TO ([FileGroup2], [PRIMARY])

 

Note: A Partition scheme can only be created for file groups, and not individual data files.

Each file group defined in TO(<list of file groups>) corresponds to VALUES(<list of values>) in partition function. This scheme defines that:

1. [FileGroup2] will contain data for Values <= 2010 and,
2. [PRIMARY] will contain data for Values > 2010

We have now created the partition function and partition scheme, but we haven’t linked these to any tables yet. This can be done while creating the Table. A partition scheme can be applied to more than one table.

 

Step 3: Creating a Partitioned Table – Once he partition scheme is defined, a table can be created using the partition scheme. It is done by specifying the partition scheme in the “ON” clause while creating the table. Only partition scheme need to be specified, you don not need to specify a partition function as this is already defined in partition scheme:

— © 2011 – Vishal (http://SqlAndMe.com)

 

— Step 3. Creating a Partitioned Table

 

CREATE TABLE Table_Orders

(

      OrderID     INT,

      Name        VARCHAR(20),

      OrderDate   DATE

)

ON ordersPartScheme (OrderDate)

That’s all folks, data inserted to ‘Table_Orders’ will be partitioned according to ordersPartFunc, and will be stored according to ordersPartScheme.

Let’s insert some data to check if the partitions are created successfully.

INSERT INTO Table_Orders (OrderID, Name, OrderDate)

VALUES (1, 'Vishal', '2011-07-22')

— Will go to Partition 2 -> [PRIMARY] File Group

 

INSERT INTO Table_Orders (OrderID, Name, OrderDate)

VALUES (2, 'Vishal', '2010-06-22')

— Will go to Partition 1 -> [FileGroup2] File Group

 

INSERT INTO Table_Orders (OrderID, Name, OrderDate)

VALUES (3, 'Vishal', '2009-05-22')

— Will go to Partition1 -> [FileGroup2] File Group

 

SELECT      OrderID, Name, OrderDate

FROM        Table_Orders

Result Set:

OrderID     Name                 OrderDate

———– ——————– ———-

3           Vishal               2009-05-22

1           Vishal               2011-07-22

2           Vishal               2010-06-22

 

(3 row(s) affected)

You can use sys.partitions catalog view to check number of partitions and rows for a table as below:

SELECT      partition_id, object_id, partition_number, rows

FROM        sys.partitions

WHERE       object_id = OBJECT_ID('Table_Orders')

Result Set:

partition_id         object_id   partition_number rows

——————– ———– —————- ——————–

72057594038910976    5575058     1                1

72057594038976512    5575058     2                2

 

(2 row(s) affected)

sys.partitions can tell how many rows are present in each partition, you can use $PARTITION function to identify a partition for a row. $PARTITION can be used $PARTITION.partition_function_name(expression):

SELECT      OrderID, Name, OrderDate,

            $PARTITION.ordersPartFunc(OrderDate)

            AS 'Partition'

FROM  Table_Orders

Result Set:

OrderID     Name                 OrderDate  Partition

———– ——————– ———- ———–

3           Vishal               2009-05-22 1

1           Vishal               2011-07-22 2

2           Vishal               2010-06-22 2

 

(3 row(s) affected)

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe