we have a FACT table with 35 billion rows, partitioned on daily level.
In a test environment we put a clustered colmnstore index on this table. Storage reduced with 300%, reading data became much faster and inserting became a little faster. No updates are doen on this table.
Now we have a second FACT table with 7 billion rows, partitioned on month.
Daily there are around 7 billion inserts and 1.5 million updates. These updates can be done on the last 12 partitions.
We want to add a clustered columnstore index on this table. But CCI and mass updates don't go well together I read.
Is it a possibility to create a second table, with same partitioning but without CCI and then switch 12 partitions to this temporary table?
Do the updates and inserts there and switch the partitions back to the original table? Or should this temporary table also have the same CCI?
Are there other solutions to this problem?