During the release of SQL server 2012 , one major announcement was support for Clustered ColumnStore Index (CCI). When it was first released one thing that caught my eye was the speed at which queries run and reduction in the size of the table.
The drawback was usability of feature in real world. When you create the CCI you cannot insert any data so you need to come up
with different workaround that I am sure some of you probably tried. That is probably why its adoption in
warehouse was limited. When Microsoft released
SQL 2014, they made CCI updatable but still non-clustered index were not updatable. 2014 solved half of the problem.
Third time lucky In SQL Server 2016, tables with CCI can now enforce referential
integrity through foreign key relationship.
These tables can now have additional indexes on them and allows addition
of data to a table that has a Non-clustered ColumnStore index without any
modification to the index (previously you need to drop and recreate the index).
You will also be able to create columnstore index on disk and memory-optimized
table.
One thing
that you cannot do is MERGE functionality still disabled. Hey, I am ok with
this for now and I am sure Microsoft will work in the future to support it.
Other added
features are
- Support for snapshot isolation level
- Support to run reorganize the index
- Support for filtered index on Non-Clustered Columnstore Indexes (Note this feature is only is available on Non-clustered Columnstore indexes)
- A lot of enhancements on batch mode processing. Sort, aggregation with multiple distinct functions (count, AVG and etc) and more is now candidate for batch mode processing. In SQL 2014, batch mode was only available for parallel queries.
No comments:
Post a Comment