Solved

HOW TO ALTER  PARTITION FUNCTION IN SQL SERVER 2005?

Posted on 2009-07-12
10
638 Views
Last Modified: 2012-05-07
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?
0
Comment
Question by:venk_r
  • 6
  • 4
10 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24836735
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
 
LVL 8

Author Comment

by:venk_r
ID: 24836781
Iam getting the below error when I execute this
The multi-part identifier "t1.value" could not be bound.

0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24836786
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
 
LVL 8

Author Comment

by:venk_r
ID: 24836796
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24836797
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24836811
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
 
LVL 8

Author Comment

by:venk_r
ID: 24836818
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
 
LVL 8

Author Comment

by:venk_r
ID: 24836839
Do I need to destroy the whole partition and create it from scratch with new boundaries?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24836840
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
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24836845
>> 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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now