Partitioning An Existing Table With A Columnstore Index?

1

Hi,
does it make sense to additionally partition a table (FactTable with 5-10 million rows) with a clustered columnstore index? Example: Partition by Year + Month in separate Filegroups / Files?
Or is the Clustered Columnstore Index with any NCX enough?

Thanks‌‌
Regards
Nicole :roll_eyes:

2

I don't think you need to do any additional partitioning. Column store indexes are partitioned into different segments of 1 million rows each automatically.

3

You should partition the table based on how you query it. If you (almost) always specify a date (year and month) value or range when querying the data, then partition the table by date. This gives you the best partition elimination, which is what you're looking for to reduce overhead.

There's no real overhead to this partitioning, since the clus columnstore index has to be built on some value anyway.