What is a column store Index, why should a column store index needs to be created, when should a column store index be created?

Problem: What is a column store Index, why should a column store index needs to be created, when should a column store index be created?

Solution:

If you want to know more about an index, and why is an index basically needed for. Please go through this article. what is an index?

Starting from SQL Server 2012 and above, you can create a column store indexes. Starting 2014 and above there can be a clustered column store, as well non-clustered column store indexes.

What is a column store Index?

When data is stored in a table, like inserts or updates are happening. If an index is present on the table SQL server tries to sort the input based on the column and store the information in small pages which are general row store Indexes. If the same index is specified in a columnar format like instead of storing the data in a b-tree structure it stores in a horizontal format. Where each set of column store unit is called a segment (approx. 1 million rows), a table can have multiple row groups called segments which are created on a column are called as column store index. For a compression aspect, a row group should have more number of rows and a for an in-memory operation it should have less number of rows.

Why should a column store index be created?

Think of a scenario where you want to improve data storage issues, you can compress the table data by 10X.

When you want to improve the selects on a column based result sets, this will be more efficient than a row based index.

Real time reporting of an operational database where you want to analyze point in time data.

Reduce I/O and Reduce data storage significantly

Batch execution improves query performance, typically 2-4x, by processing multiple rows together.

Queries often select only a few columns from a table, which reduces total I/O from the physical media.

When should a column store index needs to be created?

When ad hoc queries from an analytics database are slower this column store index can improve retrieval process.

Use a clustered columnstore index to store fact tables and large dimension tables for data warehousing workloads. This improves query performance and data compression by up to 10x. See Columnstore Indexes for Data Warehousing.

Use a nonclustered columnstore index to perform analysis in real-time on an OLTP workload. See Get started with Columnstore for real time operational analytics.

In tomorrows article lets see how to create one and what would be a best use case.