Review ColumnStore Index in SQL Server 2016

Microsoft development team has been introduced ColumnStore indexes technologies in SQL Server 2012 onward and now they are guiding us to which feature in SQL Server 2016 could help us in our workload. The most important things is that ColumnStore indexes are the significant technology of SQL Server where Microsoft development team is trying their best to improve this technology in every new release of the product.

SQL Server 2016 provides the ability to create a columnstore index on top of a memory-optimized table also which was introduced in SQL Server 2014 to allow a complete table to stay in memory all the time.  

This is the same code base technology which has been delivered in SQL Server, Azure SQL Server and VM Servers also as the services. The main objective of ColumnStore indexes to use in analytics and can achieve very high performance without make any changes in the running applications.

Before using ColumnStore indexes, we are simply using row store model which is the most common traditional model for any database/data warehouse project in SQL Server. If we want to improve the performance of any query which is based on row store model then we have to chance their indexes as Columnstore which convert the mode from row store, storage of the data to columnstore model.

The query run faster after building a columnstore index on the table, it compressed our data at column store level instead of row level which is the key factor to give us an amazing query performance. 

Query performance is much-much faster by building a columnstore clustered index because they have a special processing system due to of improved compression, reduced I/O and more data can easily fits in memory optimized for CPU utilization also.

Some point to be remember -

1. We do not need to have table with Primary Key when we create a column store index because in this situation a Columnstore Index and Primary Key can be on different index. 
2. If we are using non-clustered columnstore index and clustered columnstore index on data tables then they will carry different structures
3. For clustered columnstore index, the table itself changes its internal structure quite differently compared to a normal table with non-clustered columnstore index.