In order to use something you have to have a simple guidance, a map, which will show you The Big Picture.
And here is the little "pocket" guidance with all major highlights:
Columnstore Indexes in SQL Server 2016.
Description
Columnstore Indexes (CSI) are data structures, which aggregate stored data not by Rows, but by Columns.They are not very good for Index Seek operations (OLTP), but very good for Index Scans (OLAP).
CSI are modifiable, but CRUD operations are behave differently:
- Bulk Insert: When inserting more than 102,400 rows data go directly to Columnstore;
- Insert: When number of inserted rows is less than 102,400, new rows will be inserted they into a Delta Storage;
- Delete: Rows are not actually deleted from the CSI, they are just marked to be deleted;
- Update: Rows are not actually updated. Updated rows are marked as deleted and then new modified rows are inserted into Delta Storage;
Categorizing
- Updatable CSI can be Clustered and Non-Clustered;
- CSI can be built on a Disk-Based table and on In-Memory optimized table.
Main Features
- It can be Filtered;
- Can include all or just specified columns;
- For Disk-Based tables it can have Compression Delay (in Minutes). Good for OLTP, might decrease fragmentation.
- Max degree of parallelism can be control for the index usage;
- CSI can be partitioned;
- “COLUMNSTORE” is Default compression level. Old data can be over compressed by “COLUMNSTORE_ARCHIVE”;
- Can be created on temporary table;
- CSI can be disabled or rebuilt by “ALTER INDEX” command;
Limitations
- Can have no more than 1024 columns;
- Constraints are not included into non-clustered CSI;
- Can be created only on a table, not on a view;
- CSI can’t be Altered. For any changes it has to be re-create;
- CSI can’t have Included columns;
- CSI can’t have Sparse columns;
- CSI are sorted by compression algorithm, not by ASC/DESC;
- CSI can’t be combined with Page/Row compression, Replication, Filestream;
CSI Timeline
SQL Server 2012
- Read-only non-clustered CSI;
SQL Server 2014
- Updateable clustered CSI;
SQL Server 2016
- Updateable non-clustered CSI;
- Btree index on a clustered CSI;
- Snapshot isolation and read-committed snapshot isolation;
- CSI on a memory-optimized table;
- CSI supports filtering;
No comments:
Post a Comment