What is Column Store Index?

Answer:

Column Store Index store columns in data pages as opposed to rows which was store in Row Store architecture. In ordinary index, rows are stored in disk pages but with column store index, columns are stored in separate set of the disk pages, and so it is faster. The query optimizer considers the column store index as a data source for accessing data just like it considers other indexes when creating a query
plan. It is optimized for the improved and fast warehouse queries processing. Since in this case there is no need to read all the columns of a table, hence it provides significant savings in disk I/O and more data can fit into memory.

In Column Store approach, data is analyzed by columns. So, the lower the data cardinality i.e. the more repeating values a column has, the higher its compression rate will be. It uses the Vertipaq compression engine technology (it is also the compression engine in Power Pivot) to store columns than traditional
indexes. In Row Store approach, all indexed data from each row is put together on a single page, and data in each column is spread across all pages in an index. In a column-store index, the data from each column is kept together so each data page contains data only from a single column.