This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(35.2, 51%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKB%3C/text%3E%3C/svg%3E)
I'm currently working on extending database partition to a few more partitions. For instance we currently have FG1 [File Group], FG2, FG3, FG4, FG5 and planning to add FG6, FG7, FG8, FG9. Will there be a problem, if execute the below all at once?. My confusion is on How NEXT USED works? As per explanation "In a partition scheme, only one filegroup can be designated NEXT USED" but in the below we gave FG6, FG7, FG8 as NEXT USED. If this is a problem, How to get around it?
ALTER PARTITION SCHEME {PartitionScheme} NEXT USED FG6 GO
ALTER PARTITION FUNCTION {PartitionFunction}() SPLIT RANGE(5) GO
ALTER PARTITION SCHEME {PartitionScheme} NEXT USED FG7 GO
ALTER PARTITION FUNCTION {PartitionFunction}() SPLIT RANGE(6) GO
ALTER PARTITION SCHEME {PartitionScheme} NEXT USED FG8 GO
ALTER PARTITION FUNCTION {PartitionFunction}() SPLIT RANGE(7) GO
and so on....
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(108.80000000000001, 46%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAM%3C/text%3E%3C/svg%3E)
Hi KamalPresnaBoopathy-0588,
Was your issue resolved? Did the answer help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.
Best Regards,
Amelia
The NEXT USED filegroup of each partition scheme determines the filegroup the new partition created on when the function is SPLIT. You'll need to set a NEXT USED filegroup for scheme(s) before splitting the function unless the next used has already been set.
@Dan Guzman - Thank you for your answer!. Do you mean i should alter the scheme(s) before splitting the partition function? In below, All belongs to a same scheme - PartitionScheme1 and function - PartitionFunction1
ALTER PARTITION SCHEME {PartitionScheme1} NEXT USED FG6 GO
ALTER PARTITION SCHEME {PartitionScheme1} NEXT USED FG7 GO
ALTER PARTITION SCHEME {PartitionScheme1} NEXT USED FG8 GO
ALTER PARTITION FUNCTION {PartitionFunction1}() SPLIT RANGE(5) GO
ALTER PARTITION FUNCTION {PartitionFunction1}() SPLIT RANGE(6) GO
ALTER PARTITION FUNCTION {PartitionFunction1}() SPLIT RANGE(7) GO
There is only one NEXT USED for the partition scheme at a given time and it is unset once a new partition is created with SPLIT. If you were to run the script in your comment, the first partition (value 5) would be created on FG8 and the remaining split statements will fail because there is no NEXT USED filegroup.
It's best to simply set the next used as desired immediately before splitting the partition function, like the script in your initial question.