I have received calls from my DBA friend who read my article SQL SERVER – 2005 – Introduction to Partitioning. He suggested that I should write a simple tutorial about how to horizontal partition database table. Here is a simple tutorial which explains how a table can be partitioned. Please read my article Introduction to Partitioning before continuing with this article. Now let us continue to understand how to do Database Table Partitioning.
Step 1: Create New Test Database with two different filegroups
I have written a 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 folders 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 partitions. For our example, let us assume that first 10 records are stored in one filegroup and the rest are stored in different filegroup. The 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
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 the column name to be used with a 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 Tables
This step is optional but highly recommended. Following example demonstrates the creation of the 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 the 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 the 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 (https://blog.sqlauthority.com)
138 Comments. Leave new
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
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
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?
Yes Kenneth… In order to make use of partitions effectively, the query’s where clause should have the column information associated to partitions.
Since the query wont have the account information , it will have to check all partitions to get the data assuming the partitions are created on Account
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.
Is it possible to create a partition using multiple columns in SQL Server 2005?
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!!
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
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
Before creating a partition schema and function, how can I check if they already exists???
The article about the partitioning is very helpful. I need your help to partition the existing table.
thanks
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?
Hi,
Need your help for partion my table
i want to know how i can create a partition for my existing table?
the sample you provided creates the table
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.
Can we create partition table with Primary Key column other than Partition key column
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
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 :)
@ 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).
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
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