Partitioned Existing table

I have a table which is 800 GB and I need to partition it by year

This is a current table

CREATE TABLE [dbo].[MessageInbox]
(
    [Id] [INT] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL,
    [Subject] [VARCHAR](250) NULL,
    [MessageFrom] [VARCHAR](50) NULL,
    [MessageText] [VARCHAR](MAX) NULL,
    [DateReceived] [DATETIME] NOT NULL,
    [DateCreated] [DATETIME] NOT NULL,
    [ProfileId] [INT] NOT NULL,
    [IsRead] [BIT] NULL,
    [InstanceId] [INT] NULL,
    [msgType] [VARCHAR](25) NULL,
    [Tags] [VARCHAR](100) NULL,
    [excerpt] [VARCHAR](500) NULL,
    CONSTRAINT [PK_Inbox]
        PRIMARY KEY CLUSTERED ([Id] ASC)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
              ALLOW_PAGE_LOCKS = ON
             ) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO

This is how I though in partitioning

```
CREATE PARTITION FUNCTION [PartitioningByYear] (DATETIME)
AS RANGE RIGHT FOR VALUES
( '20150101',
'20160101',
'20170101',
'20180101',
'20190101',
'20200101',
'20210101',
'20220101',
'20230101',
'20240101'
);

---DROP PARTITION FUNCTION [PartitioningByYear];
CREATE PARTITION SCHEME PartitionByYear
AS PARTITION PartitioningByYear
TO
(
Year2014,
Year2015,
Year2016,
Year2017,
Year2018,
Year2019,
Year2020,
Year2021,
Year2022,
Year2023,
Year2024
);

CREATE TABLE [dbo].[MessageInbox]
(
[Id] [INT] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL,
[Subject] VARCHAR NULL,
[MessageFrom] VARCHAR NULL,
[MessageText] VARCHAR NULL,
[DateReceived] [DATETIME] NOT NULL,
[DateCreated] [DATETIME] NOT NULL,
[ProfileId] [INT] NOT NULL,
[IsRead] [BIT] NULL,
[InstanceId] [INT] NULL,
[msgType] VARCHAR NULL,
[Tags] VARCHAR NULL,
[excerpt] VARCHAR NULL
) ON PartitionByYear (DateCreated);

CREATE CLUSTERED INDEX CI_MessageInbox1
ON dbo.MessageInbox (Date

The primary key doesn't need to be the same as the clustering key. The default when creating a primary key is to also make it the clustered index, but that isn't required.

Try this for the CREATE TABLE statement:

CREATE TABLE [dbo].[MessageInbox]
(
    [Id] [INT] NOT NULL
        IDENTITY(1, 1)
        NOT FOR REPLICATION
        PRIMARY KEY NONCLUSTERED,
    [Subject] [VARCHAR](250) NULL,
    [MessageFrom] [VARCHAR](50) NULL,
    [MessageText] [VARCHAR](MAX) NULL,
    [DateReceived] [DATETIME] NOT NULL,
    [DateCreated] [DATETIME] NOT NULL
        INDEX MessageInbox_cx CLUSTERED,
    [ProfileId] [INT] NOT NULL,
    [IsRead] [BIT] NULL,
    [InstanceId] [INT] NULL,
    [msgType] [VARCHAR](25) NULL,
    [Tags] [VARCHAR](100) NULL,
    [excerpt] [VARCHAR](500) NULL
) ON PartitionByYear (DateCreated);

That will make the [id] column the primary key, and the [DateCreated] column will be the clustered index.

Without knowing very much about your data, I'd say it would probably be helpful to add data compression to your table and non-clustered indexes. You can do that by adding the following to the CREATE TABLE statement:

CREATE TABLE [dbo].[MessageInbox]
(
    [Id] [INT] NOT NULL
        IDENTITY(1, 1)
        NOT FOR REPLICATION
        PRIMARY KEY NONCLUSTERED
        WITH (DATA_COMPRESSION = PAGE),
    [Subject] [VARCHAR](250) NULL,
    [MessageFrom] [VARCHAR](50) NULL,
    [MessageText] [VARCHAR](MAX) NULL,
    [DateReceived] [DATETIME] NOT NULL,
    [DateCreated] [DATETIME] NOT NULL
        INDEX MessageInbox_cx CLUSTERED
        WITH (DATA_COMPRESSION = PAGE),
    [ProfileId] [INT] NOT NULL,
    [IsRead] [BIT] NULL,
    [InstanceId] [INT] NULL,
    [msgType] [VARCHAR](25) NULL,
    [Tags] [VARCHAR](100) NULL,
    [excerpt] [VARCHAR](500) NULL
) ON PartitionByYear (DateCreated);

StackExchange Database Administrators Q#295307, answer score: 7