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
I wish to get your article on how to partition based on day such as 1,2,3…31 and use the ring structure rotating window and do not required merge and split.
the data will be kept for 1 month, and i am thinking this structure meets my requirement as I can age the data in each partition which consists of 1 day of data before the next month comes.
But the problem is the date field in the table is not use in most of the user query but there are other non cluster index (composite indexes) in the exisitng huge table. Will this a tradeoff?
example:
table 1
id1 (PK)
filed1
filed2
filed3
filed4
update_dm
insert_dm
non cluster index is id1, field1,field2
but i will age the data based on insert_dm
user will query the data based on non cluster indexes.
ETL will base on insert_dm too.
the data is huge and over 20 billions of rows per month, so, do you think, if I partition it based on day of the insert_dm, and use the ring structure, it is the a good option.
any suggestion and example will be great.
Awesome example – clear and concise
Be aware that under certain conditions partitioning will not be an option due to the following bug in the query optimizer:
https://docs.microsoft.com/en-us/collaborate/connect-redirect
The bug results in certain queries performing a complete index sort, rather than just using the known order of an index to determine the result.
This is very unfortunate if your table contains billions of rows.
Examples of simple queries affected by the bug:
A. “SELECT MAX(ColumnN) FROM PartitionedTable” (with a non-clustered aligned index on ColumnN)
B. “SELECT TOP 10 ColumnX, ColumnY, ColumnZ FROM PartitionedTable ORDER BY ColumnX, ColumnY, ColumnZ” (with a clustered aligned index on ColumnX, ColumnY, ColumnZ)
In our case the above queries resulted in a difference in execution duration of milliseconds vs. hours, for the exact same queries, on non-partitioned and partitioned tables respectively. :(
As far as I know Microsoft has no plans to fix this bug in current releases of SQL Server, and has no plans to fix it in the next major either.
So for an enterprise solution that allows for you to:
-prioritize data in the same table on physical storage (because all data has to be available online, but only some is frequently queried)
-keep the physical prioritization transparent to the layers above the database (because you already have existing applications using the database, and/or is using ORMs in the data access layer)
-keep your indexes aligned to utilize PARTITION SWITCH (because your maintenance window for moving less relevant data to low priority storage is limited)
-use TOP N/ORDER BY style queries on a large amount of rows (because who doesn’t :) )
… you will have to look elsewhere.
Hi ALL,
using: Sqlserver 2008 enterprise edition
we have database partitioned basing on date [one partition for each month]. Now i want to shrink all the data files [one file per partition] in partitions except the current month partition, that is i have to shrink all the data files in partitions till dec 2010.
I am trying to get the partition name ,file group name, file name and the partition range value from below system views , but cannot find the approprtiate column to join
sys.partition_range_values
sys.partition_schemes
sys.partition_parameters
sys.partition_functions
from sys.partitions.
My main requirement: to retreive database file names in the file groups till last month.
Please help me.
Thanks in advance
Hello Pinal,
I want to partition my 500 GB database into multiple LUNS, the method would be like group of tables…
Like: All tables related to PMS should be in one LUN
All tables related to PMO in another
All tables related to RND in another so on….
Currently DB in two files on one filegroup .
Could you please suggest the steps.
Thanks in advance
hi,
i have a large table and have thousands of rows, i want to partition this table but the problem is this table has lot of references with other tables if i re-create this table using partition schema then i lost all references so my question is without affecting anything can i put this table in a partition?
Hi,
it is possible to create two partition on a single table?
Dear Pina,
How to revert back from Partioning to Single Level Database.
Example: DB Name Shop
c:\data\shop.mdf (200MB)
d:\data\tran.ndf (100MB) clustered index
e:\data\hist.ndf (50MB) clustered index
l:\data\shop_log.ldf.
I have to convert my database to
c:\data\shop.mdf
l:\data\shop_log.ldf
I have done every thing..like I removed clustered index and and shrink file but when i go for deletion it says “Can not delete file or filegoup because it is not empty.
please help me.. in a step by step.
Just to sya there is no partioning
only FileGroups.
Hi Pinal
how to select from indivisual partition.
I mean i have one table that has four partitioning.
Year1,Year2,Year3,Year4
how should I, select only one year1
hi, how can you resolve error like this. thanks.
ALTER TABLE SWITCH statement failed. The specified partition 13 of target table ‘jfcdw_prd.dbo.FactSales_CK’ must be empty
Interesting thread…
I have a requirement to set-up an MS SQL Server 2005 (or 2008) database to hold significant amounts of transaction data. One table needs to hold 26 Gigabytes of data and the other, 3 Gigabytes. This is for data querying of the transaction data.
First question is ‘Can it be done?’ in MS SQL Server.
Would I need to partition the tables over a number of hard drives to make this work?
What typical hardware configuration/approach would be recommended if the answer is YES?
Thanks in advance folks for any help, much appreciated…
Regards
Mark.
Thank you for the excellent articles. These have been a life saver several time.
I have partitioned my table by a DateTime field “RecordTime” and have partitioned it by MONTH
Two questions:
1. Is there a query to retrieve the field name on which a table was partitioned ?
2. Is there a query to retrieve the partition ranges? In my example I am expecting to see the list of current partition ranges like Jan2011, Feb2011
Thanks.
Will
how to create a partition function. And give the syntax pls
HI PINALKUMAR DAVE,
When I created Partition for my database then :
Partition function can only be created in Enterprise edition of SQL Server. Only Enterprise edition of SQL Server supports partitioning.
How can I solve with this situation? please explain to me
I am very appreciated !
Kevin
Here is the complete solution:
————————————————————————————————-
— CREATE DATABASE
CREATE DATABASE TUTORIAL
GO
————————————————————————————————-
USE TUTORIAL
GO
CREATE PROCEDURE [dbo].[sp_dim_date_monthly_partition] @nRunDate datetime = NULL
AS
BEGIN
SET NOCOUNT ON
SET @nRunDate = ‘2011-05-02’
— Constants used in this procedure
DECLARE @cRtnSuccess integer
SET @cRtnSuccess = 0
DECLARE @cRtnFailed integer
SET @cRtnFailed = 1
— Setting up variables used in this procedure
DECLARE @ErrCount int
SET @ErrCount = 0
— Obtaining the 1st at midnight of given date, if no date is given use the current date
DECLARE @nDate datetime
DECLARE @dateid numeric(8,0)
SET @nDate = DATEADD(mm, DATEDIFF(mm,0,ISNULL(@nRunDate,GetDate())), 0)
SELECT @dateid = (YEAR(@nDate) * 10000) + (MONTH(@nDate) * 100) + DAY(@nDate)
— Declare Database pathname variables
DECLARE @db_name nvarchar(4000)
DECLARE @path nvarchar(max)
— Declare Filegoup variables
DECLARE @fg_name nvarchar(max)
DECLARE @filegroup_sql nvarchar(max)
— Declare File variables
DECLARE @file_name nvarchar(max)
DECLARE @file_sql nvarchar(max)
— Declare Partition Variables
DECLARE @ps_name nvarchar(max)
DECLARE @pf_name nvarchar(max)
DECLARE @pf_sql nvarchar(max)
DECLARE @ps_sql nvarchar(max)
— Set Variable values
SET @db_name = ‘Tutorial’
SET @fg_name = ‘fg_date_monthly’
SET @file_name = ‘saledate_’ + replace(convert(varchar(7),@nDate,121),’-‘,”)
SET @ps_name = ‘ps_date_monthly’
SET @pf_name = ‘pf_date_monthly’
— DETERMINE DATABASE DATA PATHNAME
SELECT @path = [filename] FROM master..sysdatabases WHERE NAME like @db_name
IF charindex(‘\’, @path) > 0
BEGIN
SELECT @path = substring(@path, 1, len(@path) – charindex(‘\’, reverse(@path)))
END
SET @path = @path
————————————————————————————————-
— ADD FILEGROUP
IF Not Exists (SELECT * FROM sys.filegroups WHERE NAME = @fg_name)
SET @filegroup_sql =
‘ALTER DATABASE ‘ + QuoteName(@db_name,”) + ‘ ‘ + ‘ADD FILEGROUP ‘ + QuoteName(@fg_name,”)
EXEC( @filegroup_sql )
————————————————————————————————-
— ADD FILE TO FILEGROUP
IF NOT EXISTS (SELECT * FROM sys.sysfiles WHERE NAME = @file_name)
SET @file_sql =
‘ALTER DATABASE’ + ‘ ‘ + QuoteName(@db_name,”) + ‘ ‘ + ‘
ADD FILE( Name = ‘ + QuoteName(@file_name,””) + ‘,
FileName = ‘ + QuoteName( @Path + ‘\’ + @file_name + ‘.ndf’, ”” ) + ‘,
SIZE = 3072 KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024 KB
) TO FILEGROUP ‘ + @fg_name
EXEC( @file_sql )
————————————————————————————————-
— CREATE PARTITION FUNCTION
IF NOT EXISTS(SELECT * FROM sys.partition_functions WHERE NAME = @pf_name)
SET @pf_sql =
‘CREATE PARTITION FUNCTION ‘ + QuoteName(@pf_name,”) + ‘(numeric(8,0)) ‘ +
‘AS RANGE RIGHT FOR VALUES (20110301,20110401)’
EXEC( @pf_sql )
————————————————————————————————-
— CREATE PARTITION SCHEME
IF NOT EXISTS(SELECT * FROM sys.partition_schemes WHERE NAME = @ps_name)
SET @ps_sql =
‘CREATE PARTITION SCHEME ‘ + QuoteName(@ps_name,”) + ‘ ‘ +
‘AS PARTITION ‘ + QuoteName(@pf_name,”) + ‘ ALL TO (‘ + @fg_name + ‘)’
EXEC( @ps_sql )
————————————————————————————————-
DECLARE @table_sql nvarchar(max)
— CREATE A TABLE tblsaledate TO USE PARTITION SCHEME
IF NOT EXISTS(SELECT * FROM sys.objects WHERE NAME = ‘tblsaledate’ AND type = ‘U’)
SET @table_sql =
‘CREATE TABLE dbo.tblsaledate
(
sale_id int not null identity(1,1),
sale_date datetime not null,
sale_date_id numeric(8,0)
) ON ‘ + QuoteName(@ps_name,”) + ‘(sale_date_id)’
EXEC( @table_sql )
— POPULATE tblsaledate TABLE with data
DECLARE @startdate datetime
DECLARE @enddate datetime
DECLARE @thisdate datetime
DECLARE @day_id int
SET @startdate = ‘2011-03-01’
SET @enddate = ‘2011-06-01’
SET @thisdate = @startdate
SET @day_id = 1
WHILE (@thisdate <= @enddate)
BEGIN
INSERT INTO dbo.tblsaledate (sale_date, sale_date_id)
VALUES (@thisdate, CAST(convert(varchar(8),@thisdate,112) As int))
SET @day_id = @day_id + 1
SET @thisdate = DATEADD(day, 1, @thisdate)
END
—————————————————————————————————
— SET NEW FILE TO BE USED FOR NEXT SET OF DATA
DECLARE @aps_sql nvarchar(max)
SET @aps_sql =
'ALTER PARTITION SCHEME ' + QuoteName(@ps_name,'') + ' NEXT USED ' + @fg_name
EXEC( @aps_sql )
— ADD NEW RANGE FOR PARTITION
DECLARE @apf_sql nvarchar(max)
SET @apf_sql =
'ALTER PARTITION FUNCTION ' + QuoteName(@pf_name,'') + '()
SPLIT RANGE ' + '(' + CAST(@dateid as nvarchar) + ')'
EXEC( @apf_sql )
—————————————————————————————————
— IMPLEMENT ERROR HANDLING
SET @ErrCount = @@ERROR
IF (@ErrCount 0)
BEGIN
RETURN @cRtnFailed
END
SET NOCOUNT OFF;
RETURN @cRtnSuccess
———————————————————————————————–
/*
1. Data and Partition Views
============================
—-
SELECT * , $partition.pf_date_monthly(sale_date_id) FROM dbo.tblsaledate
—-
SELECT * FROM sys.partitions WHERE object_id = object_id(‘tblsaledate’)
2. Usage
===================================
—-
Exec [dbo].[sp_dim_date_monthly_partition]
—-
DROP PROCEDURE [dbo].[sp_dim_date_monthly_partition]
DROP PARTITION SCHEME [ps_date_monthly]
DROP PARTITION FUNCTION [pf_date_monthly]
*/
—————————————————————————————————
END
hi
how could i create a partition function.
how big is your table?is your partitioning data based on a time if so do use Right range
Hi,
Thanks for such a nice article, I have a question regarding the partition, that is I thought it will divide the rows between two parts through out the insertion process but it has inserted only 10 rows in first part and all other in to part2, is there a way I can equally distribute the rows among two partitions.
Thanks & Regards
Dealing with massive table is not easy,recently I had to correct a partition scheme which contains 5*12 months’ partitions .you should be very careful at the time of creating partition scheme and function since a minor mistake might cause a serious problem that correcting it may seems impossible as my case.
My advice to all is to understand its concept completely.
we’ve a massive SQL Server 2005 DB(Enterprise) comprising nearly 1500 tables which is partitioned. I ported the same database (NV_25_0.mdf) to my PC having (Standard) similar version.
But when attaching it has thrown an error saying that due to a partitioning this cannt be attached.
Pls. advise me to remove the partition and attach it to SQL Server in my PC….