SSDT in Visual Studio 2017 and ColumnStore Index

Question

In SSDT I have a table and columnstore index defined as:

CREATE TABLE [dbo].[FactBillPayTransaction] (
    [NaturalKeyText]                     NVARCHAR (500)  NOT NULL,
    [RecordEffectiveDateTime]            DATETIME2 (7)   NOT NULL,
    [SourceEffectiveUTCDateTime]         DATETIME2 (7)   NOT NULL,
    [InsertJobRunSequenceNumber]         BIGINT          NOT NULL,
    [LatestUpdateJobRunSequenceNumber]   BIGINT          NOT NULL,
    [InsertDateTime]                     DATETIME2 (7)   NOT NULL,
    [LatestUpdateDateTime]               DATETIME2 (7)   NOT NULL,
    [DeleteDateTime]                     DATETIME2 (7)   NULL,
    [ChangeControlChecksum]              NVARCHAR (32)   NOT NULL,
    .......More Columns
);
GO
CREATE CLUSTERED COLUMNSTORE INDEX [ci_cs_FactBillPayTransaction]
    ON [dbo].[FactBillPayTransaction];

When I generate scripts i get the following for SQL Server 2014:

CREATE TABLE [dbo].[FactBillPayTransaction] (
    [NaturalKeyText]                     NVARCHAR (500)  NOT NULL,
    [RecordEffectiveDateTime]            DATETIME2 (7)   NOT NULL,
    [SourceEffectiveUTCDateTime]         DATETIME2 (7)   NOT NULL,
    [InsertJobRunSequenceNumber]         BIGINT          NOT NULL,
    [LatestUpdateJobRunSequenceNumber]   BIGINT          NOT NULL,
    [InsertDateTime]                     DATETIME2 (7)   NOT NULL,
    [LatestUpdateDateTime]               DATETIME2 (7)   NOT NULL,
    [DeleteDateTime]                     DATETIME2 (7)   NULL,
    [ChangeControlChecksum]              NVARCHAR (32)   NOT NULL,
    .......More Columns   
);

CREATE CLUSTERED INDEX [ci_cs_FactBillPayTransaction]
    ON [dbo].[FactBillPayTransaction]([NaturalKeyText]);

CREATE CLUSTERED COLUMNSTORE INDEX [ci_cs_FactBillPayTransaction]
    ON [dbo].[FactBillPayTransaction] WITH (DROP_EXISTING = ON);

The two create "index" command doesn't seem right, also, why does it have a reference to one column?

Answer 1

This must be a product defect, but still the end result will be correct.

Every rowstore index must reference at least one column, that's why you have a reference to one column in the first CREATE CLUSTERED INDEX statement generated.

The most likely explanation for why the CREATE CLUSTERED INDEX is generated lies behind the partitioning requirements of columnstore indexes: data must be partition aligned, hence first you need to create a clustered index on the partition scheme (to assign each row to it's partition) then you'll need to create a clustered columnstore index with the same partition scheme using the DROP_EXISTING=TRUE clause. Thus if you had a partitioned table then the syntax would look like:

CREATE CLUSTERED INDEX [ci_cs_FactBillPayTransaction]
    ON [dbo].[FactBillPayTransaction]([NaturalKeyText])
    ON COLUMNSTORE_PARTITION_SCHEME (NaturalKeyText);

CREATE CLUSTERED COLUMNSTORE INDEX [ci_cs_FactBillPayTransaction]
    ON [dbo].[FactBillPayTransaction] WITH (DROP_EXISTING = ON)
    ON COLUMNSTORE_PARTITION_SCHEME (NaturalKeyText);