SQL Server Clustered Column Store Index (CCI) Limitations & Best Practices.

Other Indexes

You cannot create any other indexes on table having CCI. This means all Non Clustered indexes need to be dropped. This is normally not a problem as you wouldn't need other indexes if you have CCI.

Integrity Constraints

The table cannot have primary, foreign key or unique constraints. You would have to rely on your ETL process to do all the checks. If you have data integrity challenges then CCI is not recommended. If you have query performance issues and all the data integrity is handled in ETL then CCI is good option for you.

Computed Columns

The table cannot have computed columns.  Convert the computed columns to actual columns.

Updates/Deletes on Table.

Deletes and Updates are logical operations. Rows deleted are flagged deleted without physically removing from the table. Update is handled as delete and insert. Newly inserted rows go into deltastore, which is a rowstore table (heap) that keeps rows until the number of rows is large enough (~ 1 million) to be moved into the columnstore.

Lot of deletes and updates would cause fragmentation & degrade the performance. To remedy it you would have to rebuild the CCI. Rebuilding CCI also moves rows from deltastore to column store.

The DMV sys.column_store_row_groups has two columns total_rows  & deleted_rows. When percent of deleted_rows is high you would have to rebuild the index.

Index Rebuild Syntax -

Alter Index INDEX_NAME Rebuild Partition = ALL

If the table is partitioned consider rebuilding index one partition at a time.

Partitioned Tables and CCI

If the table has CCI and non-empty partition, you cannot split it. For example, if you are partitioning by day, you cannot split the partition to load data for new day.

SQL Server would give the following error -

Msg 35346, Level 15, State 1, Line 390

SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.

Workarounds

Drop the CCI, Partition Switch and Rebuild. Can be costly.

Create empty partitions in advance (so that you don't have to split) and switch-in/out partitions.