The clustered columnstore index which shipped with SQL Server 2014 is busy revolutionizing the Data Warehousing landscape in SQL Server. When creating a Fact Table in a data warehouse you will almost without question put a Clustered Columnstore (CCI) on the table instead of the traditional Clustered Index.
You get numerous benefits when using a CCI index. Below are a few of these benefits.
- 90 % compression on the data
- Lightning fast aggregate queries
- The index is write-enabled. You can thus run insert, update and delete queries against the table unlike the Non-Clustered Columnstore index in SQL Server 2012
The clustered columnstore index changes the internals of the table structure completely. Instead of the traditional row based heap type storage the clustered columnstore index stores the data in a columnar fashion.
If you have not used Clustered Columnstore Indexes yet then this is definitely worth trying out on your Data Warehouse. Stay posted for more blogs on this topic.