Partitioned table and Partitioned Columnstore index

Question

I have a partitioned table like this:

CREATE TABLE [dbo].[OrderDetailMessage]
(
    [OrderDetailMessageId] [BIGINT] IDENTITY(1,1) NOT NULL,
    [Id] [UNIQUEIDENTIFIER] NULL,
    ...
)
ON [PS_BusinessDate](BusinessDate)
WITH (DATA_COMPRESSION = ROW); 
GO 

I also have non partitioned primary key like this:

ALTER TABLE [dbo].[OrderDetailMessage] 
    ADD CONSTRAINT [PK_OrderDetailMessage] 
        PRIMARY KEY CLUSTERED ([OrderDetailMessageId] ASC)
            WITH (DATA_COMPRESSION = ROW)
ON [PRIMARY];

Now, I want to create a partitioned columnstore index:

CREATE NONCLUSTERED COLUMNSTORE INDEX [csindx_OrderDetailMessage] 
ON [dbo].[OrderDetailMessage] ([OrderDetailMessageId], [Id], ...)
WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0, DATA_COMPRESSION = COLUMNSTORE_ARCHIVE)
ON [PS_BusinessDate](BusinessDate);

I get this error:

Msg 35316, Level 16, State 1, Line 172
The statement failed because a columnstore index must be partition-aligned with the base table. Create the columnstore index using the same partition function and same (or equivalent) partition scheme as the base table. If the base table is not partitioned, create a nonpartitioned columnstore index.

I just want to understand why? I have done the same thing for other tables and did not face this kind of error.

Answer 1

The error is because the table is not partitioned. The creation of the non-partitioned clustered primary key constraint changed the table organization from a partitioned heap to a non-partitioned table with a clustered index.

If you create the primary key constraint as non-clustered, the table will remain a partitioned heap and allow creation of the columnstore index:

ALTER TABLE [dbo].[OrderDetailMessage] ADD  CONSTRAINT [PK_OrderDetailMessage] PRIMARY KEY NONCLUSTERED 
(
    [OrderDetailMessageId] ASC
)WITH (DATA_COMPRESSION = ROW)
ON [PRIMARY];
GO

If you want a clustered primary key and a partitioned columnstore index, the clustered index must be partitioned. The implication is BusinessDate must be added to the PK since unique partitioned indexes require the partitioning column to be an index key column like the example below. As to whether or not this is a viable option for your situation depends on your queries and use case for partitioning.

ALTER TABLE [dbo].[OrderDetailMessage] ADD  CONSTRAINT [PK_OrderDetailMessage] PRIMARY KEY NONCLUSTERED 
(
     [OrderDetailMessageId] ASC
    ,[BusinessDate]
)WITH (DATA_COMPRESSION = ROW)
ON [PS_BusinessDate](BusinessDate);