To add an additional partition to an existing partitioned table, we have to follow below steps...
See Also - Create a Partition & use in Table/Index (Existing/Not)
NOTE: it is not mandatory to create new/separate file & file group to add an additional partition.
1. ALTER PARTITION SCHEME
2. ALTER PARTITION FUNCTION
If you think to use a new File Group with a new File, Create a File Group and File first. BUT this is Optional.
Create a File Group and a new File to use for the New Partition.
File Group:
ALTER DATABASE Partition_Test ADD FILEGROUP FG_FII_2019_01;
File to this FileGRoup:
ALTER DATABASE Partition_Test ADD FILE
(
NAME = F_FII_2019_01,
FILENAME = 'C:\MSSQL\Data\Partition_Test \F_FII_2019_01.ndf',
SIZE = 1048576KB,
FILEGROWTH = 1048576KB
) TO FILEGROUP FG_FII_2019_01;
Now let ALTER the partition ... [Asume that, our partition is based on Date/Period column]
1. ALTER PARTITION SCHEME: we have to mention the file group name which will store this partition data
ALTER PARTITION SCHEME PS_Partition_Test
NEXT USED [FG_FII_2019_01];
2. ALTER PARTITION FUNCTION: in the function definition, we have to put the range value which separates the data for this partition.
say, we will store 2019-January and later data in this partition: [asumming the actual definition was with RIGHT RANGE of Date]
ALTER PARTITION FUNCTION [PF_Partition_Test] ()
SPLIT RANGE (N'2019-01-01T00:00:00.000');
So, From now, all data with date 2019-01-01 and future will store the file group FG_FII_2019_01 and use the new partition range for the query.
No comments:
Post a Comment