SSDT creates a clustered index before clustered columnstore index, then drops - Why?

Question

Hi.

I've got a database project. A clustered columnstore index on a table in it. When I generate the deployment script, SSDT first wants to create a normal clustered index on the first column in the table (whatever it is, even one that's not allowed in such a construct, e.g., varchar(max)), then wants to execute the creation statement for the clustered columnstore index with the 'with(drop_existing=on)' option. This breaks down when the table's first column is not allowed to be the key column in a clustered index. But SSDT does not check for this! Hence, my deployment fails. How do I convince SSDT to stop creating a clustered index on the table when I just want to create a clustered columnstore index? Here's what the script wants to do:

CREATE CLUSTERED INDEX [egni]
    ON [Fact].[EcommGeomappedNew]([DomainName]);

CREATE CLUSTERED COLUMNSTORE INDEX [egni]
    ON [Fact].[EcommGeomappedNew] WITH (DROP_EXISTING = ON);

How do I prevent this from happening?

Thank you.

Darek

Answer 1

How do I convince SSDT to stop creating a clustered index on the table when I just want to create a clustered columnstore index?

This looks like a bug in SSDT database projects for the edge case where the first column in the table cannot be indexed. Workarounds I can think of are:

1. reorder the table columns such that the first column is indexable
2. comment out the create index in the source code and include an idempotent index create in a post-deployment script

Below is an example of the second option:

--table create source code:
CREATE TABLE [Fact].[EcommGeomappedNew] (
    [Col1] VARCHAR (MAX) NOT NULL,
    [Col2] int NOT NULL
);
GO
/*
NOTE: idempotent columnstore index create included in post-deployment script to work-around SSDT bug (17.0.62201.12090)
CREATE CLUSTERED COLUMNSTORE INDEX [egni]
     ON [Fact].[EcommGeomappedNew]
GO
*/

--post-deployment script
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'Fact.EcommGeomappedNew') AND type_desc = N'CLUSTERED COLUMNSTORE')
BEGIN
    PRINT 'Creating CLUSTERED COLUMNSTORE INDEX on Fact.EcommGeomappedNew...';
    CREATE CLUSTERED COLUMNSTORE INDEX [egni]
        ON [Fact].[EcommGeomappedNew];
END;
GO