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