|
|
|
Old Hand
 
Group: General Forum Members
Last Login: Thursday, July 02, 2009 8:07 AM
Points: 369,
Visits: 711
|
|
Happy new year .
I would like to add new partition function to my existing partitioned table to store the data. how can I add function to store data from
> 10812 < = 10903
> 10903 < = 10906
and so on without deletiing the whole partitioned table.
here is the existing partition function.
CREATE PARTITION FUNCTION [PFBTCFunction](INT)
AS
RANGE LEFT FOR VALUES
( 10512,10603,10606,10609,10612,
10703,10706,10709,10712,10803,
10806,10809,10812
)
-- Schema
CREATE PARTITION SCHEME [PFBTCScheme]
AS PARTITION [PFBTCFunction] TO
(
[FILEGROUP_BTC1], -- Filegroup for < = 10512
[FILEGROUP_BTC2], -- Filegroup for > 10512 and < = 10603
[FILEGROUP_BTC3], -- Filegroup for > 10603 and < = 10606
[FILEGROUP_BTC4], -- Filegroup for > 10606 and < = 10609
[FILEGROUP_BTC5], -- Filegroup for > 10609 and < = 10612
[FILEGROUP_BTC6], -- Filegroup for > 10612 and < = 10703
[FILEGROUP_BTC7], -- Filegroup for > 10703 and < = 10706
[FILEGROUP_BTC8], -- Filegroup for > 10706 and < = 10709
[FILEGROUP_BTC9], -- Filegroup for > 10709 and < = 10712
[FILEGROUP_BTC10], -- Filegroup for > 10712 and < = 10803
[FILEGROUP_BTC11], -- Filegroup for > 10803 and < = 10806
[FILEGROUP_BTC12], -- Filegroup for > 10806 and < = 10809
[FILEGROUP_BTC13], -- Filegroup for > 10809 and < = 10812
[FILEGROUP_BTC14] -- Filegroup for > 10812
)
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: 2 days ago @ 12:23 AM
Points: 119,
Visits: 537
|
|
You need to Split the range...twice.
1. add 1 new filegroups
2. add a file to the filegroup
3. Alter your partition scheme to include the added new filegroup with NEXT USED
4. alter your partitioning function with SPLIT RANGE (10903)
5. Repeat 1-4 for the bigger boundary 10906
Check BOL for the exact syntax for ALTER Partition Function and Scheme.
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 6:06 PM
Points: 1,152,
Visits: 5,637
|
|
sunny Brook (1/2/2009)
You need to Split the range...twice.
1. add 1 new filegroups
2. add a file to the filegroup
3. Alter your partition scheme to include the added new filegroup with NEXT USED
Alter Alter Partition Scheme PartitionSchemeName
Next Used NewFileGroupName;
4. alter your partitioning function with SPLIT RANGE (10903)
Alter Partition Function Partitionfunctionname()
Split Range ('10903')
5. Repeat 1-4 for the bigger boundary 10906
Check BOL for the exact syntax for ALTER Partition Function and Scheme.
Added to what above poster said..
MJ
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Thursday, July 02, 2009 8:07 AM
Points: 369,
Visits: 711
|
|
Thanks
Yes, I am going through testing phase. I am doing the same thing what you have mentioned. One more question.
If you noticed that in my last filegroup [FILEGROUP_BTC14] stores the data greater than 10812 ( year 2008 month 12). what happend if the data got stored 100901 (year 2009 month 01) in that file group.
Will the split function automatically move January 2009 data to new filegroup ?
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: 2 days ago @ 12:23 AM
Points: 119,
Visits: 537
|
|
I assume that it will automatically move the data to new partition.
To make sure it is really the case, you can run the following before and after your splitting operation to Verify whether the rows number of the OLD last partition is changed (assuming there are data rows in there for the new partition)
SELECT partition_number,rows,filestream_filegroup_id
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='YOUR Partitioned table'
|
|
|
|
