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.