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,
How select command will work after partitioning ,while retrieve the data from the table?
Once the partitions are created on the tables , the information is stored in the SQL Server metadata tables.
Now when the select queries are fired , the metadata information is compared with the where clause and appropriate partitions are only scanned for retrieval of data and not all the partitions.
Think of it like this.. India is made up of 28 states… Relate each of the state as partition… Now if I need information of a person residing in Gujarat , the query will hit only the people information staying in gujarat as opposed to all 28 states and thereby improving performance of the query
Very nice tutorial about creating Partition in SQL Server. Could you please explain how to check the existing partition’s various details like on which column the partition has been created,etc on a database and how to remove the partition that has been created.
hi pinal,
partition column and clustered index key column is different. that time i created partioned for table. but table–>properties–>storage doesn’t show partition. but if i use select * from sys.partitions where object_name(object_id)=’tablename’. it show partioning except clustered index..what is the difference?
Thanks in advance for you help
hi pinal,
partition column and UNIQUE clustered index key column is different. that time i created partioned for table. but table–>properties–>storage doesn’t show partition. but if i use select * from sys.partitions where object_name(object_id)=’tablename’. it shows partioning except clustered index..what is the difference?..is this correct way to partition?
advance THANKS for you help
very useful .thanks
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N’TestDB’)
DROP DATABASE TestDB;
for what purpose we are using it
kindly tell me
This is being used in order to check the existence of database. If the TestDB exists , the same is being dropped.
Hi every one,
How to view the data of any specific partition ?
Thanks
Manish
I got the answer of my question,
Select $partition.TESTDB_PARTITIONRANGE(id) AS [Partition Number],* from testtable
where $partition.TESTDB_PARTITIONRANGE(id)=1
if some one is having any other or better way, please share.
Thanks
Manish
It is very helpful and useful guide for beginners. Excellent Job.
Thanks Pinal. Keep it up.
I am new to using SQL server but I know with your help I will understand it fast. Please can you send me something because am a beginner and I really want to learn. Thank you.
Hi Dev,
We have 3 years of data in more than 30 tables based on Date partition. I mean we are creating one partion for each business day. Now we want the purge the data which is 18 months older. How do I efficiently purge the data ? Can you please help us on it in SQL Server 2005.
Hi Dave,
How Can I insert records on daily basis in a partitioned table.
A data warehouse table which I update on daily basis now I created five partition on it one for each year and the last partition is empty.
I need to know the best practice in this regard.
Alia
Thanks for this excellent article (and several others). I have used this as a base for a walthrough on backup and restores of partitioned databases.
Hi Dave,
I am created partitioned table using datetime,
I defined range for month upto one year ‘2011-01-01′,’2011-02-1′,’2011-03-1’
And I created a Partitioned Table.
when I Looking into sys.partitions
SELECT partition_id, object_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID(‘partitionedtablet’)
order by partition_Number
______________________________________________________________
*** I am getting twise or thrise of the partion_number of same number,
How and why??
And some having rows and other Partition row of same number does not have rows, but index column saying 0,1 and 2.
Can anyone explain please……………..
Ramesh Chandler.
Hi Pinal, What are Disadvantages having table Partition? is Insert,Update and delete slow?
Thanks
Tharindu Dhaneenja
Dear pinal,
I am regularly reading your sql blogs,
the main problem in your blog is you are not specifying that which edition of sql server contain the particular concept. ex: partitioning is possible only in enterprise edition but you didnt mention it anywhere,
So pls form next time keep in mind to tell edition of sql server.
Here is an interesting question for you Dave,
I have a partitioned table with a clustered index on start_dt which is a datetime type field.
The same table has a few more datetime fields one of them being customer_call_start_date.
When creating an index on this table and having a key on the customer_call_start_date is it better to write it this way:
CREATE NONCLUSTERED INDEX [myIndex] ON [Schema].[table]
(
[Column1] ASC,
[customer_call_start_date] ASC
)
ON [Partition_Scheme_Partition]([START_DT])
or this way:
CREATE NONCLUSTERED INDEX [myIndex] ON [Schema].[table]
(
[Column1] ASC,
[customer_call_start_date] ASC
)
ON [Partition_Scheme_Partition]([customer_call_start_date])
Thank you
Andrea
i have very big table which i want to implement partition as you explained but how should i truncate the data which is older than some period as automatic job
Create a procedure that deletes old data based on date column and schedule that procedure to run periodically
if i 2 partition(p1,p2) i want to truncate only p2 partition as it will populated with older data so ,what will be the sql condition to truncate only parition p2 on that table.
Very nice simple example. Thanks Dave.
Hi Dave, thanks for the simple tutorial. I just have a question which I’m not sure if you have answered it already. How does Partition help in performance when you have a normalized schema with 1 main table and 4 child tables all connected with a unique id (bigint).
basically on our schema the important thing is a datetime field named WEEK because this determines the chronological order of records however this field is only present on the Main/Parent table. How does partitioning affect all our Query statements where they are joined together with the child tables?
Thank you in advance.
Pinal, this process is fine for new tables , but how do I create a partition on an existing table and how is the new partion updated regularly ?
Lian, We just went through this recently (see blog below for more details).
When you apply your partition function sql server will reorganize the data into files to match your function. be aware that this can take some time!
As new data is written into the database, is is written to the correct file/partition.