What is Columnstore Index?

Up until now, every index we have discussed stores data row by row — these are called rowstore indexes. Columnstore indexes take a completely different approach: they store data column by column. This design is optimized for analytical queries that aggregate large volumes of data.

In this tutorial, you will learn:

• What a columnstore index is and how it differs from rowstore
• Why column-oriented storage is better for analytics
• The concept of column segments and row groups
• Compression benefits of columnstore indexes
• When to use columnstore indexes

To understand columnstore indexes, you first need to see the fundamental difference between how rowstore and columnstore organize data.

Analytical queries typically have three characteristics that make columnstore indexes shine:

1. They Read Few Columns but Many Rows

-- This query reads ALL rows but only needs 1 column (amount)
SELECT SUM(price) FROM order_items;

2. Compression is Much Better

When the same data type is stored together, compression algorithms work much more effectively:

Columnstore indexes typically achieve 10x compression compared to rowstore.

3. Batch Mode Processing

Columnstore indexes enable batch mode execution, where the query engine processes data in batches of up to 900 rows at once instead of one row at a time.

Columnstore indexes organize data into two key structures:

Row Groups

Data is divided into chunks called row groups, each containing up to approximately 1,048,576 rows (about 1 million rows).

Column Segments

Within each row group, each column's data is stored as a compressed column segment.

Segment Elimination

Each column segment stores the minimum and maximum values it contains. When a query has a WHERE clause, SQL Server can skip entire segments that cannot contain matching data — this is called segment elimination.

SQL Server offers two types of columnstore indexes:

When to Use Each

Here is what you should remember about columnstore indexes:

Rowstore vs Columnstore Quick Decision

In the next tutorial, you will learn the detailed process of how SQL Server builds a columnstore index.