Microsoft development team has been stated that Columnstore indexes can achieve up to 100x better performance on analytics and data warehousing workloads and up to 10x better data compression than traditional rowstore indexes. Columnstore indexes reduce IO and optimize in-memory operations through high data compression, columnstore elimination, rowgroup elimination, and batch processing.
ColumnStore index is one of the best features in SQL Server 2016. Before introducing this, all indexes have B-Tree structures which use rowstore for the data storage but ColumnStore indexes use both types (rowstore and columnstore) of data storage.
How does Data Storage work?
There are two compression techniques for data storages such as columnstore and rowstore which are used to reduce the IO required to execute analytics queries and therefore improve query performance.
Columnstore Data Storage – In this storage, data is logically organized as a table with rows and columns but physically stored in a column-wise data format.
A columnstore index physically stores most of the data in columnstore format. In columnstore format, the data is compressed and uncompressed as columns.
There is no need to uncompress other values in each row that are not requested by the query. This makes it fast to scan an entire column of a large table.
Rowstore Data Storage - rowstore is data that is logically organized as a table with rows and columns, and then physically stored in a row-wise data format. This has been the traditional way to store relational table data such as a heap or clustered B-Tree index.
A columnstore index also physically stores some rows in a rowstore format called a deltastore. The deltastore, also called delta rowgroups, is a holding place for rows that are too few in number to qualify for compression into the columnstore. Each delta rowgroup is implemented as a clustered B-Tree index.
A deltastore is a holding place for rows that are too few in number to be compressed into the columnstore. The deltastore is a rowstore.