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
Hello
if i create new coulums and new sql query (select, insert, update or delete) after i have do partition, Should i do partition again. Or my partition will see my new coulums and my new sql query. Also i can create new table too.
Thanks
Hi Pinal,How can I mention 50% records instead of mentioning first 10 records should store in one filegroup and rest should be stored in different filegroup?
If you dont know exactly how many records will be inserting into partioned table then you cant say first 10 records,then i would like to menion % instead of fix numbers.
Please can you reply with answere.
Imran
Hi,
I have gone through your tutorial and havinf sme question. I have one table where it’s aroung 500 million records now after reading your article i want o use partition.I have two date columnn in this table one is Recd_Date and another is Entered_Date.Now i want to create partition based on entered date and that should be monthly.I am afraid to do that becauase this table is currently using for production.
And i do not have any file group in the database so needyour suggestion on this.
Sanjay
hi Pinal,
I found the data partitioning article interesting and tried to implement for my appliaction. The impletmentation discussed in this article is done.
My doubt is regarding the deletion part, in the site “http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx “, after creating a staging table the older data is switched and later the stagign table is dropped…
Can you suggest any alternative mthod available to drop partion with lesaa complexity.
Thanks in advance
Rupa
Hi,
To gain perf. improvement you would need to have an index on the partition key as Pinal Dave statues. The queries would need to be using that column as well.
Even though switching partition in and out of partition table to staging tables are quite complicated it’ s not too complicated. And it’s REALLY fast! Because it’s just a meta data change.
So – as one of you asked. I would remove archival data to other server by one of the following alternatives, depending on network capacity among other parameters. 1) switching the old partitions to a staging table.
2) Copying the staging table data to
2.1a) another “staging” db on same server
2.1b) another db on the other archival server
3) if 2.1b then done. If 2.1a make backup of staging db, move backup file to other server, restore it there to a staging db on that server, move data to archival db between db on hist.server
I have brought a file group offline in SQL Server 2005, Now I don’t know how to bring it back online ? Please help, due to this I am unable to take the backup of that particular file group.
Hello Brijesh,
Use the following command:
RESTORE DATABASE database_name FILEGROUP=filegroup_name
Regards,
Pinal Dave
Hello Pinal Dave,
Nice to read you article. I have created partation after reading your article. But i need some advice or code by which i can create Dynamic Partation. Like if in one table i have 4 month data in a year then i should have 4 partation .After one month if one more month is add in the table then it dynamically add one more partation and also if any previous month data is not aviliable or get deleted then remove that partation from database. Please help me to achive this.
Hello sir,
Your Article On partitions is excellent. It’s really Helpful me.
I just want to know sir, I have one Question.
How can i Partition On Two different On same table
For Example
I Want To Create two Partition.
1)On year Column means (1994 to 1999)
Inside it another partition
2) On Car Column means(Toyota,Hyundai,Bmw)
Pls if possible reply soon
Thank you Sir
Hi Pinal,
I have renamed a table which was involved in table partition, After rename, partition function points to new name. Will there be any issues in long run during switchout or drop old partition when a table is renamed.
I haven’t tested it as the table has more than 100 million records
Please suggest.
Regards
Suresh kumar
Hello Pinal Dave,
As per your article what will happned if i create ID as primary key. As i executed
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)=’TestTable’;
after making ID as primary key i found that one more partiton increased also number of rows in newely add partition is total count of table .Is it correct ? if not then how do we implement Unique cluster index on primary key with partition. Please advice.
hi,
i am having table with 1 lakh records, i want to do partition on this table based on the column with name status.
i want to move all separated and active records to two different partitions.
please guide me how to do this ?
thanks,
jagan
i m running the code for partition as
use master
go
create database testdb
on primary
(name=test_part1, filename=’c:\partitionpractice\test_part1.mdf’,size=4mb,maxsize=100,filegrowth=1),
filegroup testdb_part2
(name=testdb_part2,filename=’c:\partitionpractice\testdb_part2.mdf’,size=2mb,maxsize=100,filegrowth=1)
go
use Northwind
create partition function partfunc1 (int)
as
range left
for values(10)
use Northwind
create partition scheme mypartschm
as partition partfunc
to ([primary],testdb_part2)
after runing the partition scheme i get the error As
Msg 208, Level 16, State 58, Line 2
Invalid object name ‘testdb_part2’.
i m running the code for partition as
use master
go
create database testdb
on primary
(name=test_part1, filename=’c:\partitionpractice\test_part1.mdf’,size=4mb,maxsize=100,filegrowth=1),
filegroup testdb_part2
(name=testdb_part2,filename=’c:\partitionpractice\testdb_part2.mdf’,size=2mb,maxsize=100,filegrowth=1)
go
use Northwind
create partition function partfunc1 (int)
as
range left
for values(10)
use Northwind
create partition scheme mypartschm
as partition partfunc
to ([primary],testdb_part2)
after runing the partition scheme i get the error As
Msg 208, Level 16, State 58, Line 2
Invalid object name ‘testdb_part2′.
pls reply to this comment as the above comments mail id is Wrong
More imp You created database “TestDB” and you are searching in the “Northwind” for the existence of your filegroup. and Please change the Partition schema definition to use the Partition Function.
Instead of using partfunc (which is not exists) to partfunc1 (Which you created).
When I partitioned a table, I noticed the below:
1. The Primary key should include the partition key on a Partitioned Table.
2. But if I am creating this Primary key on the Primary Filegroup or not on the partition, it is letting me do so without including the partition key. Is this a good practice to have index/ Primary key not on Partition. Please advice.
For Horizontal Table Partitioning, please refer to this article at
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/ms345146(v=sql.90)#sql2k5parti_topic27.
This article explains each steps. I found it very helpful.
Hi!
We have a very large table partitioned by month.
Current month is partitioned by days, older only by months.
Partitioning is automated. all works smoothly.
One month-partition holds about 500 mio. records. Older partitions are read-only.
We want to get rid of the oldest one. I mean not switch to an empty one, we really and completely want it GONE without a trace.
do we really need to copy all data into an empty, stand-alone table???
Is there no faster way to drop the partition/filegroup? Anything?
Thank you so much for your ideas!
Andrea
Andrea,
Will you please let me know how you guys implemented the below automation process? As i need to implement the same in my business. Please reply back ASAP.
“Current month is partitioned by days, older only by months.Partitioning is automated.”
Thanks in Advance….
Hi
How can i do the table partitioning on the existing database.
Hi Faisal,
Is it on existing table?? if so let me know
Your artical is very helpful.
thanks,
hi pinal…
we are planning the partitioning in our database…but we are not clear about the performance benefits..can u please show how and where can i see those performance benifits..?
regards
Sharath