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’