Creating columnstore Index on existing partitioned table with 800+ million rows on SQL Server 2017

Question

I have partition table (on date) with B-Tree clustered index which contains more than 800 million rows.

I would like to create a clustered columnstore index on this table in place of existing clustered index, what would be the most efficient way?

Does this affect my existing primary key which has been created with B-tree clustered index?

Is there anything else do I need to do to make my columnstore index align with existing partition of table?

Please guide.

 CREATE TABLE [dbo].[ORDHDR](
        [DATE_DWID] [bigint] NOT NULL,
        [VERSION] [bigint] NOT NULL,
        [LOCATION_DWID] [bigint] NOT NULL,
        [START_LOC_DWID] [bigint] NOT NULL,
        [DESTINATION_LOC_DWID] [bigint] NOT NULL,
        [XFY_ID] [bigint] NOT NULL,
        [START_DWID] [bigint] NOT NULL,
        [END_DWID] [bigint] NOT NULL,
        [START_REQ_DWID] [bigint] NOT NULL,
        [END_IYF_DWID] [bigint] NOT NULL,
        [CREATED_AT_DWID] [bigint] NOT NULL,
        [TIME_OF_IPB_DWID] [bigint] NOT NULL,
        [DATAREC_NUM] [int] NOT NULL,
        [REQUEST_FOR_DATA_TRANSFER] [varchar](30) NULL,
        [DATAPCKT_NUM] [varchar](6) NOT NULL,
        [INTERNAL_NUM_FOR_SUPPLY] [varchar](30) NULL,
        [SOURCE_SUPPLY] [varchar](60) NULL,
        [RECORD_MODE] [varchar](1) NULL,
        [ORD_TYPE] [varchar](3) NULL,
        [APO_ORD] [varchar](12) NULL,
        [APO_APPLICATION] [int] NULL,
        [SUPPLY_CATEGORY] [varchar](12) NULL,
        [CONVERTABLE_ORD] [varchar](1) NULL,
        [ORDSTATUS_OUTPUT] [varchar](1) NULL,
        [ORDSTATUS_INPUT] [varchar](1) NULL,
        [PARTIAL_DELIVERY_STATUS] [varchar](1) NULL,
        [FINAL_DELIVERY_INDICATOR] [varchar](1) NULL,
        [STATUS_DEALLOCATED] [varchar](1) NULL,
        [STATUS_RELEASED] [varchar](1) NULL,
        [STATUS_FIXED] [varchar](1) NULL,
        [STATUS_STARTED] [varchar](1) NULL,
        [ORD_COMPONENT_ISSUED] [int] NULL,
        [PARTIALLY_CONFIRMED] [varchar](1) NULL,
        [FINAL_CONFORMATION] [varchar](1) NULL,
        [ORD_PLNG_TYPE] [int] NULL,
        [ORD_STATUS] [int] NULL,
        [START_TIME_OF_ACTIVITY] [varchar](15) NULL,
        [END_DATE_OF_LATEST_ACTIVITY] [varchar](15) NULL,
        [FLAG] [varchar](1) NULL,
        [EDW_CREATE_DATE] [datetime] NULL,
        [EDW_UPDATE_DATE] [datetime] NULL
    ) ON [ORD_PS]([DATE_DWID])

    GO

    CREATE UNIQUE CLUSTERED INDEX [ORD_HDR_PK] ON [dbo].[ORDHDR]
    (
        [DATE_DWID] ASC,
        [VERSION] ASC,
        [LOCATION_DWID] ASC,
        [START_LOC_DWID] ASC,
        [DESTINATION_LOC_DWID] ASC,
        [XFY_ID] ASC,
        [START_DWID] ASC,
        [END_DWID] ASC,
        [START_REQ_DWID] ASC,
        [END_IYF_DWID] ASC,
        [CREATED_AT_DWID] ASC,
        [TIME_OF_IPB_DWID] ASC,
        [DATAREC_NUM] ASC,
        [DATAPCKT_NUM] ASC
    )

Answer 1

Since a clustered columnstore index has only columns and no keys, you'll need to change the existing unique clustered index to a clustered columnstore index to convert the rowstore table into a columnstore and then create a new non-clustered b-tree index to enforce uniqueness.

This can be accomplished with the DROP_EXISTING=ON clause of CREATE CLUSTERED COLUMNSTORE INDEX followed by creation of the new index.

--change existing clustered index to clustered columnstore
CREATE CLUSTERED COLUMNSTORE INDEX ORD_HDR_PK ON [dbo].[ORDHDR]
    WITH(DROP_EXISTING=ON) ON [ORD_PS]([DATE_DWID]);

--rename columnstore index to a more meaningful name
EXEC sp_rename 'dbo.ORDHDR.ORD_HDR_PK','ccidx_ORDHDR', 'INDEX';

--create new non-clustered unique index
CREATE UNIQUE NONCLUSTERED INDEX [ORD_HDR_PK] ON [dbo].[ORDHDR]
    (
        [DATE_DWID] ASC,
        [VERSION] ASC,
        [LOCATION_DWID] ASC,
        [START_LOC_DWID] ASC,
        [DESTINATION_LOC_DWID] ASC,
        [XFY_ID] ASC,
        [START_DWID] ASC,
        [END_DWID] ASC,
        [START_REQ_DWID] ASC,
        [END_IYF_DWID] ASC,
        [CREATED_AT_DWID] ASC,
        [TIME_OF_IPB_DWID] ASC,
        [DATAREC_NUM] ASC,
        [DATAPCKT_NUM] ASC
    ) ON [ORD_PS]([DATE_DWID]);