Add additional partition to an existing partitioned Table/Index, SQL Server

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.