A SQL Server index type that stores data organized by column rather than by row, enabling highly efficient compression and dramatically faster analytical query performance.
- Columnstore indexes are optimized for data warehousing and analytical workloads: Queries that scan large volumes of data across a few columns benefit most.
- SQL Server supports both Clustered Columnstore Indexes and Non-Clustered Columnstore Indexes: Clustered Columnstore Indexes replace the row-based clustered index and store the entire table in columnar format, while Non-Clustered Columnstore Indexes coexist with row-based tables.
- Columnstore achieves 5–10x or better compression ratios compared to row-based storage: It compresses similar values within each column to reduce storage requirements.
- Batch mode execution automatically used with columnstore indexes: It processes thousands of rows at once rather than row-by-row, dramatically improving CPU efficiency.
- Updateable columnstore indexes use a delta store (row-based): This buffers recent inserts before compressing them into column segments, and DBAs should monitor delta store size.
- Common use cases include: OLAP reporting, operational analytics (HTAP), historical data archives, and real-time analytics on transactional tables.
- Related terms: Rowstore Index, Batch Mode Processing, Data Warehousing, OLAP, Index Rebuild.
