Columnstore Index Vs RowStore Index
What is the Columnstore Index?
1. Columnstore Index is a data structure that is used to store and manage the data in the columnar database.
2. Used for storing and querying large data warehouse fact tables.
3. Query processing is 10X faster as opposed to traditional row-oriented storage.
4. Provides 10X data compression over the uncompressed data size.
5. Beginning SQL Server 2016, columnstore indexes permit operational analytics. The capability to run performant real-time analytics on a transactional workload.
Why should we use the Columnstore Index?
1. Significantly reduce DWH storage cost.
2. It offers better performance than a b-tree index.
3. High compression rates improve query performance by using a smaller in-memory footprint.
4. Batch execution advances query performance, typically by 2X-4X times, through processing multiple rows together.
5. I/O bottlenecks in the system are minimized/eliminated, and memory footprint is reduced knowingly.
6. Use a clustered column store index to store fact tables and large dimension tables for DWH workloads. Improves query performance by 10X times.
7. Columnstore index stores each column in a separate set of disk pages, instead of storing multiple rows-per-page as data by tradition has been stored.
When to prefer ColumnStore Index over RowStore Index?
Thinking about this question, we see that both indexes can coexist in the same environments. The difference is that Column Store indexes are more suitable in OLAP (On-Line Analytical Processing) tables and RowStore indexes are more suitable for OLTP (On-Line Transaction Processing) tables. We use Column Store indexes into fact tables and tables of a large amount of data like Data Warehouse. We can use non-clustered Column Store indexes to make Real-Time Operational Analytics in OLTP environment.
Important
1. Remember once you add a columnstore to any table, the data cannot be deleted, inserted or updated – it’s READ ONLY.
2. This incident is not a major concern as it’s used for DWH application.
3. Use partition to avoid rebuilding the index.
References:
https://docs.microsoft.com/en-us/sql/...
https://www.sqlservercentral.com/blog...
https://www.sentryone.com/blog/meliss...
https://logicalread.com/sql-server-co...
https://aleson-itc.com/en/revoluciona...