I have a fact table which is a clustered columnstore index. It is also:

-partitioned by a single data date column.

-has a nonclustered, aligned, composite unique index (PK) on 6 INT columns, plus the data date above (like PeriodDate,CustomerId,LocationId,ShopId,ExtraId,...,DocumentId).

When somebody creates a clustered columnstore index it is recommended to create a clusterd rowstore index first, then create the clustered columnstore with the DROP_EXISTING option, so the columnstore will align according to the previously dropped rowstore (awkward but this is what it is https://tsql.tech/a-script-to-automatically-align-columnstore-indexes-to-enhance-segment-elimination-and-hence-performances/ )

My question is, if the fact table is partitioned by one (non unique) date column, is it enough to (or should i) align the clustered columnstore to the data date only (creating the temporary rowstore on single column), or should i include all the other 6 integer clustered composite PK in that rowstore index, to have truly sorted data along the PK?

Queries will tend to use the date only mostly, and few of the first columns in the PK.

  • 1
    Your best bet is to test it both ways. How big is the table?...my hunch is you'll see nearly no difference unless you're dealing with some seriously big data.
    – J.D.
    Commented Jun 2, 2021 at 11:12
  • not big at the moment, but don't have the space right now to test, although with time, the facts will be billions of rows ... with smaller set, yes, couldn't spot anything
    – Avi
    Commented Jun 2, 2021 at 11:55
  • 1
    I've done some minimal testing with columnstore indexing on tables that were in the billions of rows, and would still lean towards my hunch, especially if most of the time your predicates will be only on the date column.
    – J.D.
    Commented Jun 2, 2021 at 12:06


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.