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.