MERGE RANGE PARTITION SQL SERVER 2005

Merging Partition states moving the data into an exiting partition or to new file group (Forward only means to next file group or Partition).

 

The merged partition resides in the filegroup that originally did not hold boundary_value. boundary_value is a constant expression that can reference variables (including user-defined type variables) or functions (including user-defined functions). It cannot reference a Transact-SQL expression. boundary_value must either match or be implicitly convertible to the data type of its corresponding partitioning column, and cannot be truncated during implicit conversion in a way that the size and scale of the value does not match that of its corresponding input_parameter_type. (BOL)

 

 

Alter partition function easydb_partition_yearly_function()

merge range(‘2008-01-31 23:59:59.997’)

SPLIT RANGE PARTITION SQL SERVER 2005

SPLIT RANGE PARTITION.

SPLIT RANGE Partition splits the data from exiting partition to new added Filegroup or partition.

Let’s say we have an existing database partition setup, we need to move some of the data from one partition to newly added partition.

Steps 

1. Create a New FileGroup

USE [master]

GO

ALTER DATABASE [xxx] ADD FILEGROUP [FG2008_Arch]

2. Add File to Newly Created FileGroup

ALTER DATABASE [easy] ADD FILE

( NAME = N’PSCH2008_Arch’,

      FILENAME = N’I:\MSSQL.1\MSSQL\DATA\PSCH2008_Arch.ndf’ ,

      SIZE = 2048KB , FILEGROWTH = 1024KB )

TO FILEGROUP [FG2008_Arch]

GO

Let say we want to partition Year 2008 Data to my newly created FileGroup.

To do so we need to Adds a filegroup to a partition scheme or alters the designation of the NEXT USED filegroup for the partition scheme.

ALTER PARTITION SCHEME easydb_partition_yearly_schema

NEXT USED ‘FG2008_Arch’

Every partition have a boundry set so we need to set the split boundry

ALTER PARTITION FUNCTION easydb_partition_yearly_function()

SPLIT RANGE (‘2008-06-31 23:59:59.997’)

All data on and before of date and time ‘2008-06-31 23:59:59.997’  will move to new filegroup.

To check partiton has splited check by executing the below code

 

SELECT * FROM sys.data_spaces 

–Get the data_space_id of the newly created filegroup

 

SELECT count(*) FROM dbo.tablename where $Partition.easydb_partition_yearly_function(partitioned columnname)=data_space_id of the newly created filegroup

 

Now Set back the Next Used Partition

ALTER PARTITION SCHEME easydb_partition_yearly_schema

NEXT USED ‘FG2009’

How to create partitioned table and moving non-partitioned tables to partition schemes

 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

 

Lets insert some data and see how it get partitioned. 

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.