Partitioned Existing table

All we need is an easy explanation of the problem, so here it is.

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](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
) ON PartitionByYear (DateCreated);

CREATE CLUSTERED INDEX CI_MessageInbox1
ON dbo.MessageInbox (DateCreated);
GO
CREATE NONCLUSTERED INDEX IX_messageinbox_id ON dbo.MessageInbox (Id);

My questions are

1- Is this the optimal way to create the partition table ?

2- I am confused in not having the ID as primary key , is there a way to add it to primary key?

3- Having the datecreated as the key in the clustered index. isn’t this will degrade the performance and will add extra key lookups for every query?

I want to partition the table since I though it will be easier if I need to archive / drop older years. And I can create index / maintain on partitions instead of have one index on the whole table.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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);

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply