Given a simple row-based table without a PK but with a row-based clustered index like so:

Then I wish to add a column store index that is segmented in the same order as the clustered index above:

MaxDop hint to preserve order came from: here

Then the following query was used to report the min/max data_id for the PropertyId column and it the full range was reported on each of the 7 segments:

I tried making the clustered index unique which did slightly affect the reported ranges but still was not monotonically increasing.

Any ideas?

Here is a Link that accomplished the segmentation in this manner but I don’t see any difference.

Version: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)

Top Answer
Paul White

This isn’t directly supported for nonclustered columnstore indexes.

It does work for clustered columnstore.

Azure Synapse Analytics has language support for doing it in one step e.g.:

This syntax has not yet made it to the SQL Server box product, though it is available under an undocumented feature flag so perhaps it isn’t far away. It still won’t work on a nonclustered columnstore index though.

General Workaround

The best you can do is to create the nonclustered rowstore index with MAXDOP = 1, then replace it with a nonclustered columnstore index with MAXDOP = 1 and DROP_EXISTING = ON.

This isn’t guaranteed to preserve the ordering as you want, but it is highly likely:

This will give you your best chance of achieving rowgroup elimination when filtering on PropertyId.

Special Case

When the desired ordering matches the rowstore clustered index (as appears to be the case in the question), there is no need to create a rowstore nonclustered index first. The documentation says:

Note, for nonclustered columnstore index (NCCI), if the base rowstore table has a clustered index, the rows are already ordered. In this case, the resultant nonclustered columnstore index will automatically be ordered.

So, in your case, it should be enough to run only:

Metadata

You can see the min and max values for each rowgroup and column using:

Demo

<>https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8af9375320c889339475dcbb98a8c618

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.