Clustered columnstore index results in clustered index being created first

[simonsabin]

• SqlPackage or DacFx Version: 16.1.8089.0
• .NET Framework (Windows-only) or .NET Core: >Net Core
• Environment (local platform and source/target platforms): Any

Steps to Reproduce:

1. create table with only a clustered columns store index
2. deploy table
3. table is created clustered index is added and after that the clustered columnstore index is added with drop_existing.

For an existing table where columnstore is being added its a real pain

Did this occur in prior versions? If not - which version(s) did it work in? Was reported back in 2017, Steven Green checked at the time and confirmed thats the way the code has always worked, probably due to the initial implementation of column store. It was also reported on Connect if thats of any use. https://connect.microsoft.com/SQLServer/feedback/details/3120614/ssdt-clustered-columnstore-rebuild-behaviour

SQLPackage

[EdwardPollack]

There should be a way to bypass this step. The clustered rowstore allows for order to be placed on a data set before the clustered columnstore is added, but this will not always be needed. When not needed, it consumes a mountain of extra resources and time for no gain.

[simonsabin]

The random thing is that dacfx picks the first column in the table to create the clustered index on, even if thats nullable. In our recent case we also had partitioning and it fails because the first column wasn't the partitioning column

[EdwardPollack]

@simonsabin That makes zero sense and provides no value. If intelligent, it could be a useful option. This is not a hard thing to resolve, at minimum to remove this functionality so it does not waste resources and break processes.

[jspanos71]

Please resolve this issue. This breaks deployments when the first column is nullable or is unfit to be a clustered index key. We have to fall-back to manual deployments when adding clustered columnstore indexes.