Sql | Columnstore Index
A column store index is an index that was designed mainly for improving the query performance for workloads with very large amounts of data.
This type of index stores the index data in a column based format rather than row based as is done with traditional indexes.
Benifits of columnstore index
Main benefits of column store indexes: -
1. Columnstore index reduced storage costs. Column store indexes provide a very high level of compression, up to 10x, due to the fact that the data across columns is usually very similar and will compress quite well.
2. Better performance. With a smaller index footprint, due to the compression, we reduce the amount of IO we have to perform. Also because of this small footprint we can fit more of the index into memory which helps to speed up processing.
3. Queries often only query a few columns from the table. Since the data is stored in a column based format this also reduces the amount of IO that needs to be performed.
How to create a columnstore index?
Confirm Index Usage
Once the index is created we can write a basic aggregation query similar to what you would see in any data warehouse environment.
