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 Dave, can you explain me how can I partition my data warehouse table it has got one clustered composite primary key [snapshot_date_key, process_date_Key, process_unit_key, metric_key, uom_key, msr_type_key] it has got around 14 million data and all its queries are so effective. we’d like to tune it.. hence we are looking for partitioning.
kindly share your insights and thoughts about this? also revert me in case if you need additional information.
-Vivek
Hey There. I found your blog using msn. This is a really well written article.
I’ll be sure to bookmark it and come back to read more of your useful information. Thanks for the post. I’ll definitely return.
Manoj
I am new to DB, I have a database with 30gb single mdf file, and the perfromance of the database appears to be slow.
I have below queries,
1.how can i split the 30gb mdf file into many mdf files?, if this can be done, will this splitting spread the 30gb data accross multiple mdf/ndf files?
2.I have tried adding the mdf/ndf files ,under database->properties->files, if Iuncheck the autogrowth of the 30gb mdf file, will the data automatically move to the newly added mdf/ndf files?
3.can something be done in SQL, that when importing the .bak file of 30gb, data can be split accross multiple mdf/ndf files created prior to the restoration of the database backup file?
Please help me.. Any help ASAP, will be a lifesaver for me
Hi Pinal
Is it possible to partition a table using 2 column value’s instead of only 1 column for the partition function?
Consider a table with 4 columns
ID (int, primary key,
name (varchar),
Country (varchar),
region (varchar)
ex:
ID name Country region
1 rahul India karnataka
2 raj India maharastra
3 brad Usa california
4 richard Usa arizona
I want partition data combining based on country and region
1st partition data like india and karnataka
2nd partition data like india and maharastra.
3rd partition data like usa and california
4th partition data like usa and arizona
Hi Pinal
Please can you reply on my above question on multiple column partition in a table.
Yes it is possible, You need to use a Computed Column and Make sure it is Persisted Something like Country_Region and use the computed column to manage the partitions. It would be easy and efficient to use ID’s instead of VARCHAR
Hi Pinal
I need to create partition for each day. Is there a way I can create new filegroup with each partition?
What If I need to Add another Partition Scheme to the Same table / Existing Table????
How to do that???
Thanks
Hi Pinal,
Is there any way to display the rows of a table in a particular partition ?
Can do this in Sql server 2008 r2?
Hi,
How to distribute a table data which is more large data, in to more table.
You can create multiple tables and create a view on top of that.
Hi Sir,
I seen your article on Table partitioning in SQL Server,I have a requirement which is quiet tricky,I need your help with the Stored Procedure scripts for the below logic
SQL Procedure having Loop never end by itself to populate the data to the below table,its a infinite loop,just the programmer need to stop the program abruptly,But data should exists in table
table name : T_RECORD_DUMP
COLUMN 1 : ID – PRIMARY KEY COLUMN
COLUMN 2 : Insert_date – DATETIME COLUMN
TABLE Having 4 partitions p1,p2,p3,p4
Every 60sec the records to be switch between the partitions
before switching to the other partition data should be truncated.
p1 ——- p2 ———p3—–p4
1stmin 2ndmin 3rdmin 4thmin
at the end of 4th min records to be inserted to partition p1 again until 1min time frame BEFORE INSERT MUST TRUNCate(not use delete stATEMENT)
In the above scenario,partition is based on time.
I don’t have enough free time to help in your assignment. Please post on other places for quick help.
Hi Sir,
How much partition we can create in each database
Hi Pinal,
It is really useful information, I just want to know I have similar kind of requirement where my users maintain million of data every month and wish to consolidate it after years but due to excel limitation they cannot do it. Now we have made database on sql server 2008, dump whole year data when I tried to get the data from sql server it still slow. I read your topic and now I need your help how to create the data part ion
of each month or quarter then we can get the quickly. otherwise it taking ages to get the data…. Please help
[email removed]
Thanks
Farhan,
Have you tried optimizing the queries first and implement indexes on the table first??? First try opitmizing the queries and then if your database size is too much like in GB’s or TB’s then go for paritioning.
I have requirement to archive the old data in a table to a filesystem, I created partition based on the year value of the date column, now when I want to move the file to another drive sql server does not now allow to move this file as it is used by the sQL server, how should I approach moving the old partition file to out of production database server?
This is amazing tutorial. I have got one question though. I have added 3 new file-groups and split my table over 3 new file groups but it looks like my data is being added to both 3 new file-groups as well as the original PRIMARY file-group. How can I make sure my data is only on the new file-groups and not on the PRIMARY file-group.
FileGroupName Rows
——————————————————————–
FG_SESSION_1 1014144
FG_SESSION_2 2028800
FG_SESSION_3 8117760
PRIMARY 11160704
As you can see above my rows are split between 3 new file-groups but still exists on the PRIMARY file-group. I have also checked the file-group file sizes and it supports the above scenario. I have only one table on this test database.
Hi Pinal,
Is there any feature partition is available in SQL server. if it is available please provide the document for creating the feature partition
Regards,
Karthik G
Hot heard. If you come to know, please share.
Hi Dave Sir,
Nice article written by you which is very understandable but as a 1+ guy I want to know in detail.
Please help me.
Thanks,
Venkata.
I am not sure about your question.
Hi Pinal,
Nice article and easy to understand. I have few doubts.
1) How to apply Partition Key/Schema to existing table
2) How to move old data to Seconday filegroup assuming Primary is my default filegroup
3) Does Partitioning increases the LDF file size
Regards,
Shailesh G