Tables Partitioning is a new feature available in SQL Server 2005, focusing on
improving performance of large database systems and Highly Transactional Databases. Partitioning feature is only available with SQLServer2005 EE and DE.
This article focuses on how to create a partitioned table and moving non-partitioned tables to partition schemes.
Steps: Creating the Partitioned Tables
1.Add FileGroups
-
USE [Database name]
GO
ALTER DATABASE [AdventureWorks] ADD FILEGROUP[FG_od]
GO
2.Add Secondry Data Files (.ndf) and associate it with respective filegroups
-
USE [Database name]
GO
ALTER DATABASE [AdventureWorks] ADD FILE
(
NAME = N’01_31_Jan_Dec_2008′,
FILENAME = N’G:RSDBSecondryDataFiles1_31_Jan_Dec_2008.ndf’ ,SIZE = 2048KB ,FILEGROWTH = 1024KB
)TO FILEGROUP [FG_01]
GO
Once the fileGroups and secondrydatafiles have been added,Create partition fucntion and partition schemes.
3.Create Partition Function (creating partition function on datatype Int.)
-
USE [Database name]
GOCREATE PARTITION FUNCTION [PF_DBAW_ON_IDNUMBER] ( INT )
AS RANGE
LEFT FOR VALUES (130000000,260000000,520000000,1040000000,…..)
Creating partition fucntion on DateTime datatype.
-
USE [Database name]
GOCREATE PARTITION FUNCTION [PF_DBAW_ON_DateTime](DateTime)
AS RANGE
LEFT FOR VALUES
(‘20080331 23:59:59:997’,–Jan-Mar2008
‘20080630 23:59:59:997’,–Apr-Jun2008
‘20080930 23:59:59:997’,–July-Sept2008
‘20081231 23:59:59:997’,–Oct-Dec2008
‘20090331 23:59:59:997’—Jan-Mar2009
)
RANGE LEFT/RANGE RIGHT
This is probably one of the more confusing aspects of table partitioning. Partition functions are defined with either RANGE LEFT or RANGE RIGHT. One way to remember the difference is that a function with RANGE LEFT means that the partition data relative to the boundary is to the left of the boundary; RANGE RIGHT means that the partition data relative to the boundary is to the right of the boundary.
If the boundary is <= 130000000 the value will be written in First filegroup.
If the boundry is > 130000000 and <= 260000000 the value will written in second filegroup and so on.
4. Create Partition Schemes
-
USE [Database name]
GOCREATE PARTITION SCHEME PS_DBAW —name of partition scheme
AS PARTITION [PF_DBAW_ON_IDNumber]
TO (FG_01, FG_02, FG_03,FG_04,[Primary])
GO
— all partition are held as per the filegroup series and depending upon function.)
5. Create Table using partition Schemes (Partitioned Table)
-
Create table TestPartition
(
Cust_id INT,
Cust_date datetime
)
ON [PS_DBAW] ([Cust_id])
Check the table properties to confirm table is partitioned or not.
run below command to check data location.
exec sp_help TestPartition
Data_located_on_filegroup
—————————
PS_DBAW —————Partition Scheme
Check Table partition structure
-
SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID(‘TestPartition’)
Query Output:-
partition_id |
Object_id |
index_id |
partition_number |
hobt_id |
rows |
72057594058964992 |
919674324 |
0 |
1 |
72057594058964992 |
0 |
72057594059030528 |
919674324 |
0 |
2 |
72057594059030528 |
0 |
72057594059096064 |
919674324 |
0 |
3 |
72057594059096064 |
0 |
72057594059161600 |
919674324 |
0 |
4 |
72057594059161600 |
0 |
72057594059227136 |
919674324 |
0 |
5 |
72057594059227136 |
0 |
-
Declare @int int
set @int=0
while @int<10000
Begin
set @int=@int+14
BEGIN Tran
insert into dbo.TestPartition(Cust_id,Cust_date) values(@int,getdate())
commit
End
Check Table partition structure
-
SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID(‘TestPartition’)
Expected Output:-
partition_id |
Object_id |
index_id |
partition_number |
hobt_id |
rows |
72057594058964992 |
919674324 |
0 |
1 |
72057594058964992 |
4501 |
72057594059030528 |
919674324 |
0 |
2 |
72057594059030528 |
3658 |
72057594059096064 |
919674324 |
0 |
3 |
72057594059096064 |
578 |
72057594059161600 |
919674324 |
0 |
4 |
72057594059161600 |
0 |
72057594059227136 |
919674324 |
0 |
5 |
72057594059227136 |
0 |
Check data in each filegroup.
-
SELECT $partition.PF_DBAW_ON_IDNUMBER(o.Cust_id)
AS [Partition Number]
, min(o.Cust_id) AS [Min Cust_id No]
, max(o.Cust_id) AS [Max Cust_id No]
, count(*) AS [Rows In Partition]
FROM TestPartition AS o
GROUP BY $partition.PF_DBAW_ON_IDNUMBER(o.Cust_id)
ORDER BY [Partition Number]
Steps : Moving non-partitioned tables to Partitioned schemes.
Before moving non-partitioned table to partition scheme , script all relationship(FK).Once you drop the clustered index participating in FK relationship all relationship will be dropped.
-
Run sp_help tablename command to check the table status.Do not forget to check Foregin Key relationship.
-
Drop clustered index and recreate again using dataspace type as partiton scheme.
-
GO
DROP INDEX <table_name>.<index_name>
GO
Create Clustered index
BEGIN TRANSACTION
GO
CREATE CLUSTERED INDEX IX_Cust_Id ON TestPartition
(
Cust_id
) WITH( STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON PS_DBAW(Cust_id)
GO
COMMIT
Once clustered Index is created , check the properties of the table,It will show table is partitioned.
Now create all the relationship again if any.