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 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
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
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
Hooman,
Did u succeed in your project , Can you please share your knowledge ?
‘C:DataPrimaryTestDB_Part1.mdf’,
SIZE=2,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP TestDB_Part2
(NAME = ‘TestDB_Part2’,
FILENAME =
‘C:DataSecondaryTestDB_Part2.ndf‘,
i have one doubt in the post. Here ‘TestDB_Part2.ndf’ is ‘.mdf’ or ‘.ndf’?
It is .ndf
Yeah… .NDF – Secondary database file used by Microsoft SQL Server; created when the primary .MDF database becomes too large (such as when there is no disk space left); may also be manually added to the SQL Server File Group to distribute data between the two files.
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
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?
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.
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.
I like to know How I can partition my existing database.
Thanks
@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 ..
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.
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.
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?
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.
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
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
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?
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
Hello
where are answers about questions related to this topic ?
Thanks