adding 365 partitions to existing scheme and fucntion in sql server 2014

Question

adding 365 partitions to existing scheme and fucntion in sql server 2014

prem.m38

Ten Centuries

Points: 1117
More actions
December 10, 2017 at 2:35 am

#402738

Hi
My tables are partitioned on business date. My requirement is to add 365 partitions for next year to existing partition function and scheme.
1)ALTER PARTITION SCHEME[PS_D_FG1]
NEXT USED FP_COM_FG1
2) ALTER PARTITION FUNCTION [PF_DAILY_FG1]()
SPLIT RANGE (N'2018-01-01T00:00:00.000') -- New Range
GO

I can able to add only one partition at a time using above script. Could you please provide me script to add 365 partitions at a time for next year 2018.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 1

prem.m38 - Sunday, December 10, 2017 2:35 AM
Hi
My tables are partitioned on business date. My requirement is to add 365 partitions for next year to existing partition function and scheme.
1)ALTER PARTITION SCHEME[PS_D_FG1]
NEXT USED FP_COM_FG1
2) ALTER PARTITION FUNCTION [PF_DAILY_FG1]()
SPLIT RANGE (N'2018-01-01T00:00:00.000') -- New Range
GO

I can able to add only one partition at a time using above script. Could you please provide me script to add 365 partitions at a time for next year 2018.

Will you have a separate file group for each day? If so, what is the specific naming convention you want to use for the 365 days? If not, do you want all of the partitions to be assigned to the FP_COM_FG1 file group?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

prem.m38 Ten Centuries Points: 1117 More actions · Answer 2

Jeff Moden - Sunday, December 10, 2017 2:44 PM
prem.m38 - Sunday, December 10, 2017 2:35 AM
Hi
My tables are partitioned on business date. My requirement is to add 365 partitions for next year to existing partition function and scheme.
1)ALTER PARTITION SCHEME[PS_D_FG1]
NEXT USED FP_COM_FG1
2) ALTER PARTITION FUNCTION [PF_DAILY_FG1]()
SPLIT RANGE (N'2018-01-01T00:00:00.000') -- New Range
GO

I can able to add only one partition at a time using above script. Could you please provide me script to add 365 partitions at a time for next year 2018.
Will you have a separate file group for each day? If so, what is the specific naming convention you want to use for the 365 days? If not, do you want all of the partitions to be assigned to the FP_COM_FG1 file group?

Hi Jeff,

I want to add all 365 partitions to single file group only. And when I do this ,all partitioned tables using this partition scheme and function will automatically get the new partitions right ?

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 3

prem.m38 - Monday, December 11, 2017 2:25 AM
Jeff Moden - Sunday, December 10, 2017 2:44 PM
prem.m38 - Sunday, December 10, 2017 2:35 AM
Hi
My tables are partitioned on business date. My requirement is to add 365 partitions for next year to existing partition function and scheme.
1)ALTER PARTITION SCHEME[PS_D_FG1]
NEXT USED FP_COM_FG1
2) ALTER PARTITION FUNCTION [PF_DAILY_FG1]()
SPLIT RANGE (N'2018-01-01T00:00:00.000') -- New Range
GO

I can able to add only one partition at a time using above script. Could you please provide me script to add 365 partitions at a time for next year 2018.
Will you have a separate file group for each day? If so, what is the specific naming convention you want to use for the 365 days? If not, do you want all of the partitions to be assigned to the FP_COM_FG1 file group?
Hi Jeff,
I want to add all 365 partitions to single file group only. And when I do this ,all partitioned tables using this partition scheme and function will automatically get the new partitions right ?

Yes but we can "auto-magically" generate and execute all of the code necessary to do this. I just need to know what naming convention you use for the partition names in the partition function so that we can automate it.

As a bit of a sidebar, having so many partitions may actually be causing more harm to performance than good especially if you're NOT (and your not because you only use 1 file group) setting older partitions that have become static in nature to Read_Only.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)