What is a columnstore index, and when would you use one?
Quick Answer
A columnstore index stores data column-by-column instead of row-by-row, with heavy compression and batch-mode execution. It gives huge speedups (often 10×+) for analytical queries that scan and aggregate many rows over few columns — reporting, data warehouses — but it's poor for OLTP-style single-row lookups.
A columnstore index flips the storage around — instead of keeping each row together, it stores each column together, compresses it hard, and processes rows in batches. That's a massive win for analytical queries that scan millions of rows but only touch a few columns and aggregate them, so it's the go-to for fact tables and reporting. It's the wrong choice for OLTP — looking up or updating one row at a time is slow against columnstore. You can also add a non-clustered columnstore to an otherwise normal table to run analytics on live data without a separate warehouse.
- Why it's fast for analytics: only the columns you query are read; similar values compress extremely well; the engine processes rows in batches of ~900 rather than one at a time.
- Clustered columnstore — the whole table is columnstore (best for a fact table). Non-clustered columnstore — an analytical index alongside a normal rowstore table (real-time operational analytics).
- Structure: data in rowgroups (~1M rows) split into compressed column segments; segment elimination skips segments whose min/max can't match the filter.
- Not for: frequent single-row seeks/updates, tables with few rows, wide
SELECT *point queries.