Error creating a Clustered Primary Key on a partition table

I have a table that has been designed, ready to go into production and loaded up with data and I need to partition it for performance reasons. But I'm getting an error.  I need to be able to partition on a column that is not part of the clustered primary key, can this be done.  thanks in advance for your help.

table definiition before partitioning......................
CREATE TABLE [test].PartyTextDetail](
      [PublishDateTime] [datetime] NOT NULL,
      [PartySK] [int] NOT NULL,
      [AttributeSK] [int] NOT NULL,
      [AsOfDateTime] [datetime] NOT NULL,
      [NextPublishDateTime] [datetime] NOT NULL,
      [NextAsOfDateTime] [datetime] NOT NULL,
      [AttributeValue] [nvarchar](255) NOT NULL,
      [ProcessGUID] [uniqueidentifier] NOT NULL,
      [CurrentRecordInd] [bit] NOT NULL,
 CONSTRAINT [PKPartyTextDetail] PRIMARY KEY CLUSTERED
(      [PublishDateTime] ASC,
      [PartySK] ASC,
      [AttributeSK] ASC,
      [AsOfDateTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

And this is the sql to create it as a partition table....
CREATE TABLE [test].[PartyTextDetail](
      [PublishDateTime] [datetime] NOT NULL,
      [PartySK] [int] NOT NULL,
      [AttributeSK] [int] NOT NULL,
      [AsOfDateTime] [datetime] NOT NULL,
      [NextPublishDateTime] [datetime] NOT NULL,
      [NextAsOfDateTime] [datetime] NOT NULL,
      [AttributeValue] [nvarchar](255) NOT NULL,
      [ProcessGUID] [uniqueidentifier] NOT NULL,
      [CurrentRecordInd] [bit] NOT NULL,
 CONSTRAINT [PKPartyTextDetail] PRIMARY KEY CLUSTERED
(      [PublishDateTime] ASC,
      [PartySK] ASC,
      [AttributeSK] ASC,
      [AsOfDateTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON
) ON PS_WEEKLY(NextAsOfDateTime))

And this is the error message I'm getting...
Msg 1908, Level 16, State 1, Line 2
Column 'NextAsOfDateTime' is partitioning column of the index 'PKPartyTextDetail'. Partition columns for a unique index must be a subset of the index key.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
partyonAsked:
Who is Participating?
 
dqmqCommented:
Partitioning and the clustered index work hand-in-hand.  You can't partition, in the SQL Server sense, without involving the clustered index.

The only workaround of which I'm aware would be to implement a crude form of "home-grown" partitioning. For example, suppose you want to spllit your data into a different table for each  year.  Use stored procedures to evaluate the year and insert/update/delete from the appropriate table.  Use a view that joins all tables to retrieve the data.  
0
 
dqmqCommented:
>I need to be able to partition on a column that is not part of the clustered primary key, can this be done.

No
0
 
partyonAuthor Commented:
Is this just something that is just not done or is there a workaround to achive what I'm trying to do?
0
 
partyonAuthor Commented:
thanks, after much discussion with the business I was able to add the partition key to the primary key. I appreciate your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.