3

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.

0

1 Answer 1

7

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);
2
  • I got this error while creating the table Msg 1908, Level 16, State 1, Line 219 Column 'DateCreated' is partitioning column of the index 'PK__MessageInbox__0C403E8D'. Partition columns for a unique index must be a subset of the index key. Msg 1750, Level 16, State 1, Line 219 Could not create constraint or index. See previous errors.
    – sebeid
    Jul 16, 2021 at 3:50
  • 1
    to make it work , I add this CONSTRAINT PK_MessageInbox PRIMARY KEY NONCLUSTERED (ID,DateCreated) WITH (DATA_COMPRESSION = PAGE) at the end instead of PRIMARY KEY NONCLUSTERED WITH (DATA_COMPRESSION = PAGE),
    – sebeid
    Jul 16, 2021 at 4:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.