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.

Create Partition & use in Table/Index (Existing/Not), SQL Server

To use the benefit of partition, we have to create a partition scheme, partition function and then attached it to the Tabel/Index.

See also - Add additional partition to an existing partitioned Table/Index.

so follow the below example...

0. [Optional] Create FileGroup and File to use in Partition Scheme
1. Create a Partition Function.

2. Create a Partition Scheme.

3. Create a New Table using the Partition Scheme. OR

4. Existing Table: Create a Clustered Index using the Partition Scheme.

First of all, let's discuss our data/table for what we will create a Partition.

Table Definition: TestTable

ColumnName	Type
Name	nvarchar(500)
Designation	nvarchar(500)
JoiningDate	datetime

Now, we will create the partition for the Column JoiningDate

0. [Optional] Create FileGroup and File to use in Partition Scheme

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;

1. Create a Partition Function.

below partition function will separate data by three partitions. look at the last one, from 2019-03 and later all data will use the last range.

CREATE PARTITION FUNCTION [PF_TestTable](datetime)
AS RANGE RIGHT FOR VALUES
(
N'2019-01-01T00:00:00.000',
N'2019-02-01T00:00:00.000',
N'2019-03-01T00:00:00.000'
)

2. Create a Partition scheme.

NOTE: it is not mandatory to use different filegroup for a different range. If you have the different disk for I/O, then you can create three different files & filegroup on different disks. Whatever the case, you can use different filegroup for respective ranges or can use the same filegroup for all ranges.

CREATE PARTITION SCHEME [PS_TestTable] 

AS PARTITION [PF_TestTable] TO
(
[FG_FII_2019_01],
[FG_FII_2019_01],
[FG_FII_2019_01]
)

3. Create a New Table using the Partition Scheme

CREATE TABLE [TestTable] (Name nvarchar(500), Designation nvarchar(500), JoiningDate datetime)
ON [PS_TestTable] (JoiningDate)

4. Existing Table [TestTable]: Create a Clustered Index using the Partition Scheme.

NOTE: Delete the existing Clustered Index, if any.

CREATE CLUSTERED INDEX [IX_TestTable] ON [dbo].[TestTable]
(
[JoiningDate] ASC
) ON [PS_TestTable] (JoiningDate)

For more Information: Click Here.