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] GO CREATE 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] GO CREATE 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] GO CREATE 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.

1. Run sp_help tablename command to check the table status.Do not forget to check Foregin Key relationship.

2. 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.