Home > SPLIT Partitioning SQL Server 2005, SQLServer 2005 Partitioning > SPLIT RANGE PARTITION SQL SERVER 2005

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’

  1. June 15, 2009 at 5:49 am

    ehh. love it ))

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: