Clustered Columnstore Indexes (CS Index) were one of the two headline features for SQL Server 2014. Designed for tables with over 10 million rows, they allow for good performance on analytical queries without the need to explicitly specify indexes.
A downside of the 2014 version is that you can’t specify indexes. While a CC Index is much faster than a table scan on a traditional table, it isn’t anywhere near as fast as a hand-tuned covering index. So in order to support both modes, developers have been creating two tables: a normal table with B-Tree indexes and a Clustered Columnstore Index. Keeping them synchronized is of course a challenge.
In SQL Server 2016, this is no longer a problem. Level 130 Clustered Columnstore Indexes can have secondary, B-Tree style indexes just like a traditional table. These indexes support any number of columns and may be filtered.
Another issue with Clustered Columnstore Indexes is the lack of primary and foreign key support. Since the database couldn’t enforce referential integrity on a CC Index, it is up to the middle tier developer to ensure no data corruption occurred. With the ability to apply B-Tree indexes on CC Indexes, you can create “primary keys and foreign keys by using a btree index to enforce these constraints”.
Warning: “MERGE is disabled when a btree index is defined on a clustered columnstore index.”
Isolation Levels
Starting with 2016, CC Indexes support the snapshot and read-committed snapshot isolation levels. This removes the need for a reader-writer lock, which in turn allows for better performance in tables that are being actively written to.
Index Defragmentation
CC Indexes in SQL Server 2014 were designed primarily for append-only style workloads. While you can modify or delete records, this causes fragmentation that could only be resolved by rebuilding the entire CC Index. In 2016, you can reorganize the index to reduce fragmentation. Index reorganization is always an online operation, meaning that it doesn’t hold long-term locks that would prevent querying the table.
Batch Mode Enhancements
Because it uses the same storage engine, CC Indexes get the same batch mode enhancements as Non-clustered Columnstore Indexes.
