SQL SERVER – 2005 – Database Table Partitioning Tutorial – How to Horizontal Partition Database Table

January 25, 2008 by pinaldave

I have received call from my DBA friend who read my article SQL SERVER – 2005 – Introduction to Partitioning. He suggested that I should write simple tutorial about how to horizontal partition database table. Here is simple tutorial which explains how a table can be partitioned. Please read my article Introduction to Partitioning before continuing to this article.

Step 1 : Create New Test Database with two different filegroups
I have written tutorial using my C: Drive, however to take advantage of partition it is recommended that different file groups are created on separate hard disk to get maximum performance advantage of partitioning. Before running following script, make sure C: drive contains two folders – Primary and Secondary as following example has used those two folder to store different filegroups.
USE Master; GO --- Step 1 : Create New Test Database with two different filegroups. IF EXISTS ( SELECT name FROM sys.databases WHERE name = N'TestDB') DROP DATABASE TestDB; GO CREATE DATABASE TestDB ON PRIMARY (NAME='TestDB_Part1', FILENAME= 'C:\Data\Primary\TestDB_Part1.mdf', SIZE=2, MAXSIZE=100, FILEGROWTH=1 ), FILEGROUP TestDB_Part2 (NAME = 'TestDB_Part2', FILENAME = 'C:\Data\Secondary\TestDB_Part2.ndf', SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ); GO

Step 2 : Create Partition Range Function
Partition Function defines the range of values to be stored on different partition. For our example let us assume that first 10 records are stored in one filegroup and rest are stored in different filegroup. Following function will create partition function with range specified.
USE TestDB; GO --- Step 2 : Create Partition Range Function CREATE PARTITION FUNCTION TestDB_PartitionRange (INT) AS RANGE LEFT FOR VALUES (10); GO
Click on image to see larger image
Step 3 : Attach Partition Scheme to FileGroups
Partition function has to be attached with filegroups to be used in table partitioning. In following example partition is created on primary and secondary filegroup.
USE TestDB; GO --- Step 3 : Attach Partition Scheme to FileGroups CREATE PARTITION SCHEME TestDB_PartitionScheme AS PARTITION TestDB_PartitionRange TO ([PRIMARY], TestDB_Part2); GO
Step 4 : Create Table with Partition Key and Partition Scheme
The table which is to be partitioned has to be created specifying column name to be used with partition scheme to partition tables in different filegroups. Following example demonstrates ID column as the Partition Key.
USE TestDB; GO --- Step 4 : Create Table with Partition Key and Partition Scheme CREATE TABLE TestTable (ID INT NOT NULL, Date DATETIME) ON TestDB_PartitionScheme (ID); GO
Step 5 : (Optional/Recommended) Create Index on Partitioned Table
This step is optional but highly recommended. Following example demonstrates the creation of table aligned index. Here index is created using same Partition Scheme and Partition Key as Partitioned Table.
USE TestDB; GO --- Step 5 : (Optional/Recommended) Create Index on Partitioned Table CREATE UNIQUE CLUSTERED INDEX IX_TestTable ON TestTable(ID) ON TestDB_PartitionScheme (ID); GO
Step 6 : Insert Data in Partitioned Table
Insert data in the partition table. Here we are inserting total of 3 records. We have decided that in table partition 1 Partition Key ID will contain records from 1 to 10 and partition 2 will contain reset of the records. In following example record with ID equals to 1 will be inserted in partition 1 and rest will be inserted in partition 2.
USE TestDB; GO --- Step 6 : Insert Data in Partitioned Table INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1 VALUES (1,GETDATE()); INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2 VALUES (11,GETDATE()); INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2 VALUES (12,GETDATE()); GO
Step 7 : Test Data from TestTable
Query TestTable and see the values inserted in TestTable.
USE TestDB; GO --- Step 7 : Test Data from TestTable SELECT * FROM TestTable; GO
Step 8 : Verify Rows Inserted in Partitions
We can query sys.partitions view and verify that TestTable contains two partitions and as per Step 6 one record is inserted in partition 1 and two records are inserted in partition 2.
USE TestDB; GO --- Step 8 : Verify Rows Inserted in Partitions SELECT * FROM sys.partitions WHERE OBJECT_NAME(OBJECT_ID)='TestTable'; GO

Partitioning table is very simple and very efficient when used with different filegroups in different tables. I will write very soon more articles about Table Partitioning. If you need any help in table partitioning or have any doubt, please contact me and I will do my best to help you.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Database, Pinal Dave, SQL, SQL Authority, SQL Data Storage, SQL Index, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | Tagged Partitioning | 40 Comments

40 Responses

on January 26, 2008 at 3:10 pm | Reply SQL SERVER - 2005 - Introduction to Partitioning Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – 2005 – Database Table Partitioning Tutorial – How to Horizontal Partition Database Tabl… [...]
on February 15, 2008 at 10:00 am | Reply pramod bhat

hi..

how to increase performance of a table which containes data in crores…also the query which is used to retrieve data is large/big.

plz help me out…

thanks,

pramod
on March 5, 2008 at 4:22 pm | Reply Abhishek Doyle

Hi

Can anyone please help me out,

Does PARTITION TABLE help to improve performance in terms of INSERTION into a TABLE ?

I have two windows services, which pick the MESSAGE from MSMQ and inserts the data into a TABLE of SQL Server 2005, execution of any heavy query hampers insertion process of my services and pileup gets started at MSMQ end.

Would it be really useful to me if i create two partitions into my table for respective windows services ?

NOTE : Table contains billions of record
on March 11, 2008 at 8:34 pm | Reply Kenneth Leong

Thanks for the article.. got a question.

With a table with horizontal partition in place (say by id) if the select statement in your store proc does not have a where clause of account id, then that store proc won’t benefit from the partitioning right ? ie. no perf gain?
on March 19, 2008 at 9:21 pm | Reply Thien Nguyen

Is it possible for an admin to move a partition from one server to another server as part of archiving? From all the reading, it does not seem to be possible.

Thanks.
on April 22, 2008 at 11:31 am | Reply Ashley James

Is it possible to create a partition using multiple columns in SQL Server 2005?
on June 25, 2008 at 3:57 am | Reply Sameer

We have OLTP environment with 50,000 concurrent user and considering table partition option due to CPU bottleneck. We dont have nay memory issue on system but as User grows, CPU increases. Since we are moving data between partitions and we use data using Partition column, wont our CPU on Select statements on table come down and improve CPU? Got confused from article as it says there wont be significant gain if its CPU issue. Please clarify.

Thanks!!
on July 2, 2008 at 12:38 pm | Reply rrblr

We have 2 tables in our DB Schema.
Table1 is partioned by col1 and table2 is partitioned by col2.

now we have to insert values to table1 and table2 inside one transaction.

we are getting error saying
transaction cannot be used across multiple partitions.

Any thoughts on using transaction across multiple partitions

thanks
on July 22, 2008 at 12:07 am | Reply Ritesh

Hi Dave,

I wanted to check if it possible to create a Table Partition on the column which is not a primary key.
Can you please advise.

Thanks in Advance.

Regards,
Ritesh
on July 23, 2008 at 11:08 pm | Reply ali

Before creating a partition schema and function, how can I check if they already exists???
on September 8, 2008 at 10:09 am | Reply Mustaque Ahemed

The article about the partitioning is very helpful. I need your help to partition the existing table.

thanks
on September 9, 2008 at 1:08 am | Reply Scott

Hello, I have a table with 100 million rows of values ( id, date ,value) .. 90% of the time only the current month data is accessed. Nightly 50,000 rows are inserted with yesterday’s value. In the past, i would create a history table and archive the older data.. is partioning by date a good solution?
on October 4, 2008 at 12:34 pm | Reply maheshkumar

Hi,

Need your help for partion my table
on October 4, 2008 at 5:03 pm | Reply سیستم مدیریت آموزش سما

i want to know how i can create a partition for my existing table?
the sample you provided creates the table
on October 20, 2008 at 10:52 am | Reply AQ

I still need to know How I can partition my existing database. I released my application online before a week, and now I have more than 2 million records in my table. Can you lead me how I can partition my existing huge table.
on November 12, 2008 at 8:12 pm | Reply Naren Sangu

Can we create partition table with Primary Key column other than Partition key column
on November 22, 2008 at 7:31 am | Reply COBRASoft

Hi,

I have several tables with more than 2 million records. It is about invoices, invoicedetail, invoicedescription (multi-language) and invoicepayments. I get time-outs everywhere in my software due to this huge tables. Any idea if partitioning will solve this? Do you have an idea how I could partition these?

Greetings,
Sigurd
on December 1, 2008 at 12:12 pm | Reply Andy

Can you tell me how to convert an existing table into a partition table? I have some tables that need to be partitioned due to the huge amount of data stored..
Thanks in advance :)
on December 4, 2008 at 3:34 pm | Reply Andrei

@ Andy
Dave’s article is excellent, I follow the instructions step by step and works great.
Rename the your table, follow all the steps described above and at the end just insert the data from the old renamed table into newly created (the one that you just partitioned).
on December 19, 2008 at 8:59 am | Reply Hooman

Hi

I have a very large table containing almost more than 100 million records and when I want to do a search performance is very low, so I decided to use partitioning and create 4 partitions based on a CHAR field in the table.
Do you think this increases search performance?
The DB is already created and it has one file group, what can I do about that?

Could you please let me know?Thanks

Regards,
Hooman
on December 19, 2008 at 2:21 pm | Reply Hooman

Hi
I created 5 partitions for my table as the following code:

CREATE PARTITION FUNCTION ParitioningTest_PartitionRange(nchar(4))
AS
RANGE LEFT FOR VALUES (‘CARD’, ‘CBO’, ‘CHEQ’, ‘CUST’, ‘TELL’)

CREATE PARTITION SCHEME ParitioningTest_PartitionScheme
AS PARTITION ParitioningTest_PartitionRange
TO ([FG_CARD], [FG_CBO],
[FG_CHEQ],[FG_CUST],
[FG_TELL], [FG]);

Now I dont know how to move records from ‘Transaction_Summary’ table to this partitions.

I should mention that all this partitioning is for this “Transaction_Summary” which has more than 100 million records and there in a CHAR(4) field in this table called “Channel” which doesnt have an index(there is an id column which is primary key” and I want to do the partitioning based on this filed, I know if that filed was a primary ke I could say

Drop index Transaction_Summary_IXC on Transaction_Summary with (Move To [ParitioningTest_PartitionScheme] (Channel) )

But I dont know what to do in this case, could you please help me?

Regards,
Hooman
on December 21, 2008 at 1:35 pm | Reply Ajay

Hi pinal,

We are facing some problems in our company.

we have a very huge dataset ~100M and we have used partitioning for the same.
All the data was supposed to be residing in the partitions created according to the statecodes but the size of primary mdf file has increased to a bulk.
Can you suggest a solution or reason for.

Regards
Ajay
on January 6, 2009 at 4:25 pm | Reply Phani

Can i create a partition with multiple columns as my partition key. If Yes, can you give an example for me. Iam in a pressing need for that please do reply
on January 8, 2009 at 10:20 am | Reply Anoop Paul

Hi Pinal,

This article is excellant. Thanks for this.
I have a very huge table and i wish to partition this table based on a date field with out affecting other tables. How can i partition this table which has millions of records? Any impacts on partitioning a table which has data?

Thanks In Advance,
Anoop
on January 8, 2009 at 10:33 am | Reply Manu

Hooman,

Did u succeed in your project , Can you please share your knowledge ?
on February 16, 2009 at 6:10 pm | Reply vinkas

‘C:\Data\Primary\TestDB_Part1.mdf’,
SIZE=2,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP TestDB_Part2
(NAME = ‘TestDB_Part2′,
FILENAME =
‘C:\Data\Secondary\TestDB_Part2.ndf‘,

i have one doubt in the post. Here ‘TestDB_Part2.ndf’ is ‘.mdf’ or ‘.ndf’?
on February 17, 2009 at 11:31 am | Reply Prasanna

I have doubt on this, how do partitioning on the existing tables?

you have partitioned for new table but how do we know this table gets filled in future? I am not clear this point (New table partitioning) can u clear me out this?

Thanks in advance
on February 18, 2009 at 7:29 pm | Reply Mike

Is there a way to automate backup and testing restore process for all partitioned databases on a server. I have databases partioned for each month and i would like to backup only recent 3 years of data and the rest would like to backup only once in FULL. is this possible?
on February 20, 2009 at 2:47 am | Reply Mounika

Hi,

My Database is a Decision Support System.

I have table that contains 100 Million records, and each day we bulk copy and insert 300,000 more records. The main problem we are facing is as per our logic, the data for the past 25 days is only required when performing other business logic. We do not want to have archiving and moving old data to history tables, so any given day all the data for the last 2 years exist. Now my problem is when creating indexes I dont need the index to be on the previous months rather than I need the index if it can cover the last 1 month of Date range. If I do table partitioning how will be my performance increase or is there any other way round for this problem.
on March 1, 2009 at 7:06 pm | Reply Yaser

hi, i need help please can any body help,
i want to retrieve data from one table, between two given dates, if these dates are of different years example 2008 & 2009, then im getting error. im converting with the convert function with the code 101.
any help in this regard will be help full.
on March 6, 2009 at 3:08 am | Reply Minli

I like to know How I can partition my existing database.

Thanks
on March 6, 2009 at 10:46 am | Reply Imran Mohammed

@Milni,

To Start, I would say…

Create new datafiles in same or different hard disk to improve IO Performance ( if it is raid 5 or 10 then for DR ).

Again each datafile can have multiple file groups, you can have similar objects created under one filegroup, like all tables in one filegroup and all Indexes in other file group ( this gives you oppurtunity to take backup of individual filegroup, also improves performance ..
on April 1, 2009 at 6:42 pm | Reply Qainan

I would like to know, that i have 22 million records and i want to increase performance of application which design is better for me.
on April 14, 2009 at 10:42 am | Reply Husain

I have crores of records in a table and lakhs or record are added everyday.

Will partitioning improve performance drastically and if yes how many partitions would be most suitable to have on these many number of records.
on May 16, 2009 at 9:46 pm | Reply Dan

The question asked about queries not using the partion key was good I just dont see an answer.
Do youhave to query by the partition key to get the benefits of the partitions?
on June 20, 2009 at 8:05 am | Reply Xuan Thang

Hi Pinal Dave,

I have a very big database, so it takes me about 1 hours to run one report. Can I use partion to divide my database to many smaller partition by every month? Can we store the data of one month in one partition?

Example:

partition_2009_01: will store all data which insert in January.
partition_2009_02: will store all data which insert in February.
……

Thanks and Regards.
on August 13, 2009 at 7:57 pm | Reply Brijesh

Hi Pinal,

I have a requirement where a table on its own is 600GB and the data is kept for 30 days. Every weekend there is a job which triggers 40 others bulk insert jobs which pumps data into the database from different locations.
Since there is no common factor to create the partition I have decided to create it based on day
example
Every months has got 1,2,3,4……….30 or 31
so through of creating 31 file groups and attached 31 file ndf files.

I am not sure if this can be done as I tried couple of times but the partition cannot pick up values from the getdate() field…

Any help would be really appropriated!

Thanks
Brijesh
on October 5, 2009 at 7:16 pm | Reply kumar

i have more then 5 rows in one table(i,e table1) i need to copy the rows into other table (i’e table2) which have its own rows in it.
when i try
INSERT tbl_Education (
Standard,
School,
Board
)
SELECT Standard,School,
Board FROM tbl_Prev_Education

then it is just inserting one row (if tbl_Prev_Education has one row)
when tbl_Prev_Education has more then one row then it is not inserting in to tbl_Education table

anyone plz help me
on October 29, 2009 at 10:51 pm | Reply subbarao

Hi, I have table with 6 months data(11 lacs). On this table we are performing grouping. For that the query taking around 2 sec. This table already has the index. So to improve the performance I am trying to implement the partition based on date field. I want to partition the table into month wise. will it help to improve the performance?
on November 7, 2009 at 1:30 pm | Reply Shital

Hello pinal,
I have been create a partition on existing table. this table having 10 to 12 indexes. so which index I would be drop and recreate.

pleases replay on mail id.

Thanks
Shital