Rowstore vs Columnstore Index

Two very different kinds of query hit an IPL database. The app asks "show me Virat Kohli's full profile" — one complete row. The analyst asks "total runs of every team over 15 seasons of ball-by-ball data" — one or two columns from hundreds of millions of rows. Storing data row by row is perfect for the first; storing it column by column is perfect for the second.

Definition

A rowstore index stores data row by row: each page holds complete rows (id, name, team, runs together). This is the traditional format and the default in SQL Server.

A columnstore index stores data column by column: values of one column are kept together in compressed segments, so a query reads only the columns it needs.

• Row by row → great when you need the whole record (OLTP: orders, banking, bookings).
• Column by column → great when you scan and aggregate a few columns over huge tables (OLAP: data warehouses, BI, reports).
• Compressed segments → a column like team has very few distinct values (RCB, MI, CSK …), so it compresses extremely well.

Rowstore vs Columnstore — the picture

How a columnstore index is built — 4 steps

Result: SELECT COUNT(*) FROM players WHERE team = 'MI' reads only the team segment — not the whole table. Small new inserts first land in a rowstore delta store and are compressed into a row group later.

Rowstore vs Columnstore — comparison

Syntax

CREATE [CLUSTERED | NONCLUSTERED] [COLUMNSTORE] INDEX index_name
ON table_name (column1, column2, ...);

Example — a big analytics table of every ball bowled

-- The whole ball_by_ball table is stored column by column
CREATE CLUSTERED COLUMNSTORE INDEX CCI_ball_by_ball
ON ball_by_ball;

-- Rowstore table, plus a columnstore copy of two columns for reports
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_innings_team_runs
ON innings (team, runs);

How it works: after the first statement, a report such as SELECT team, SUM(runs) FROM ball_by_ball GROUP BY team reads just the compressed team and runs segments and is processed in batch mode (thousands of rows at a time) — often 10× or more faster than rowstore on very large tables.

Rule: you cannot choose columns for a CLUSTERED columnstore index — it always covers the whole table. A table can have only one columnstore index.

Common mistakes

• ❌ Adding a columnstore index to a busy OLTP table with constant single-row updates. ✅ Use it for read-heavy analytics; writes are slow.
• ❌ Writing CREATE CLUSTERED COLUMNSTORE INDEX CCI ON t (col1, col2). ✅ Clustered columnstore takes no column list.
• ❌ Expecting a gain on a 10,000-row table. ✅ Columnstore shines at millions of rows (a row group is ~1M rows).

SQL Server vs others

Columnstore indexes are a SQL Server (and Azure SQL) feature. MySQL and PostgreSQL don't have a built-in equivalent (analytics engines like ClickHouse, Redshift, Snowflake and BigQuery are columnar by design). The playground (SQLite) has no columnstore at all.

Interview tip

Q: When would you use a columnstore index instead of a rowstore index? A: For analytical (OLAP) workloads — large fact tables that are scanned and aggregated on a few columns. Columnstore reads only the needed columns, compresses heavily and uses batch mode. For transactional (OLTP) workloads that read and write whole rows, rowstore is better.