27 September 2016

Columnstore Indexes SQL 2016 in a nutshell



What is it: A columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore. Uses deltastore to reduce fragmentation of the column segments that improves columnstore compression and performance by storing rows until the number of rows reaches a threshold and are then moved into the columnstore. See Ref# https://msdn.microsoft.com/en-us/library/gg492088.aspx for details.

Performance: The columnstore index is the standard for storing and querying large data warehousing fact tables. It uses column-based data storage and query processing to achieve up to 10x query performance gains in your data warehouse over traditional row-oriented storage, and up to 10x data compression over the uncompressed data size. Beginning with SQL Server 2016, columnstore indexes enable operational analytics, the ability to run performant real-time analytics on a transactional workload. Faster query optimizers for aggregate functions, and Sorting. Aggregate pushdown is supported with or without Group By clause for both clustered columnstore indexes and nonclustered columnstore indexes. Columnstore indexes support read committed snapshot isolation level (RCSI) and snapshot isolation (SI). Columnstore supports index defragmentation by removing deleted rows without the need to explicitly rebuild the index. The ALTER INDEX … REORGANIZE statement will remove deleted rows, based on an internally defined policy, from the columnstore as an online operation. Columnstore indexes can be access on an AlwaysOn readable secondary replica. You can improve performance for operational analytics by offloading analytics queries to an AlwaysOn secondary replica.

Types: Clustered ColumnStore Index (CCI) and Non-Clutsered ColumnStore Index (NCCI)

Combination: Cannot have more than one any type of Columnstore Indexes. CCI can be combined with usual Btree indexes (rowstore based indexes). A usual Clustered index can be converted into CCI also.

Limitations: You cannot use cursors or triggers on a table with a clustered columnstore index. This restriction does not apply to nonclustered columnstore indexes so you can use cursors and triggers on a table with a nonclustered columnstore index but nonclustered columnstore indexes does not save space but do give performance improvements for OLTP. Cannot combined with page/row compression, filesteam or replication. MERGE is disabled when a btree index is defined on a clustered columnstore index. For in-memory tables, a columnstore index must include all the columns; the columnstore index cannot have a filtered condition.

Functional: Support for primary keys and foreign keys by using a btree index to enforce these constraints on a clustered columnstore index. The columnstore index does not have key columns.


Thank you,
Sameer