HOW TO ALTER PARTITION FUNCTION IN SQL SERVER 2005?

I need to alter the data partition function which has the date range of one month each(3 partitions). I need to change them to contain 2 months of data instead  on each partition. What is the best approach on doing this?
LVL 8
venk_rAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Below query will give you the Merge statements that needs to be executed.
Hope this helps
select 'alter partition function ur_partition_fn_name () merge range (' + t1.value + ')'
from (
select t1.value, row_number() over ( order by t1.value) rnum
from sys.partition_range_values t1, sys.partition_functions t2
where t1.function_id = t2.function_id
and t2.name = 'ur_partition_fn_name' ) temp
where rnum = 1

Open in new window

0
venk_rAuthor Commented:
Iam getting the below error when I execute this
The multi-part identifier "t1.value" could not be bound.

0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Oops.. Small syntax mistake.
Kindly try this one out
select 'alter partition function ur_partition_fn_name () merge range (' + value + ')'
from (
select t1.value, row_number() over ( order by t1.value) rnum
from sys.partition_range_values t1, sys.partition_functions t2
where t1.function_id = t2.function_id
and t2.name = 'ur_partition_fn_name' ) temp
where rnum = 1

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

venk_rAuthor Commented:
This time I get one more exception
Msg 402, Level 16, State 1, Line 1
The data types varchar and sql_variant are incompatible in the add operator.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Currently you have only 3 partitions and you can manually merge the second partition.

alter partition function ur_partition_fn_name () merge range ( ur_second_partition_val)

Kindly replace ur_partition_fn_name and ur_second_partition_val to get this work out now.

Anyhow this script will be helpful to you when you have several partitions with you.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Try this one out..

Dont have access to Server now and hence haven't checked out for syntax.
Kindly revert if any error comes again.
select 'alter partition function ur_partition_fn_name () merge range (''' + convert(varchar(10), value, 101) + ''')'
from (
select t1.value, row_number() over ( order by t1.value) rnum
from sys.partition_range_values t1, sys.partition_functions t2
where t1.function_id = t2.function_id
and t2.name = 'ur_partition_fn_name' ) temp
where rnum = 2

Open in new window

0
venk_rAuthor Commented:
Actually I have 4 left date range partition
CREATE PARTITION FUNCTION Trackpartitionfn(datetime)
AS RANGE LEFT FOR VALUES

('20090108 23:59:59.997',
'20090208 23:59:59.997',
'20090308 23:59:59.997',
'20090408 23:59:59.997')

And I want to create 2 months per partition now which would like
CREATE PARTITION FUNCTION Trackpartitionfn(datetime)
AS RANGE LEFT FOR VALUES
(
'20090308 23:59:59.997',
'20090508 23:59:59.997',
'20090708 23:59:59.997',
'20090708 23:59:59.997')

How would I use merge to achieve this.?
0
venk_rAuthor Commented:
Do I need to destroy the whole partition and create it from scratch with new boundaries?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
The script which I provided earlier would help you to achieve something like this

CREATE PARTITION FUNCTION Trackpartitionfn(datetime)
AS RANGE LEFT FOR VALUES

('20090108 23:59:59.997',
'20090308 23:59:59.997')

To add new more partitions you have to use SPLIT function as given below:

alter partition function Trackpartitionfn () split range ( '20090508 23:59:59.997');
alter partition scheme ur_partition_scheme NEXT USED [primary];
alter partition function Trackpartitionfn () split range ( '20090708 23:59:59.997');
alter partition scheme ur_partition_scheme NEXT USED [primary];
alter partition function Trackpartitionfn () split range ( '20090908 23:59:59.997');
alter partition scheme ur_partition_scheme NEXT USED [primary];

you have to replace ur_partition_scheme with your partition scheme name and primary with filegroup name if you have used other than primary.

Hope this helps
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Do I need to destroy the whole partition and create it from scratch with new boundaries?

Not necessary.. You can continue using the existing partition along with appropriate SPLIT and MERGE functions to obtain the necessary changes.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.