Boost SQL Server Queries with Column Store Indexes: Architecture & Benefits

Traditional row‑store databases keep multiple rows per page, while column store indexes store each column in its own set of pages, allowing only the needed columns to be read into memory.

Column store indexes are ideal for data‑warehouse workloads that involve large data loads and read‑only queries, offering up to ten‑fold query speed improvements and up to seven‑fold data compression compared with uncompressed row stores.

Row stores process data row‑by‑row; column stores use batch processing, handling many rows at once.

Row stores load entire rows into memory even if only a few columns are needed; column stores load only the required columns.

Column data, being highly similar within a column, compresses efficiently, speeding up reads.

01. Characteristics of Column Store

SQL Server reads data in page units. In a row store, each page contains all columns of many rows, limiting the number of rows per page. In a column store, each page holds values from a single column, allowing roughly ten times more rows per page and dramatically reducing I/O for queries that touch only a few columns.

Consequently, when querying a large table for a small subset of columns, column store indexes can improve read speed by thousands of times compared with row stores.

02. Physical Implementation

Data tables (heap or B‑Tree) remain row‑oriented, while column store indexes store data column‑wise. The following diagram illustrates the difference:

1. Advantages

Only the columns referenced in the SELECT clause are read, avoiding unnecessary I/O.

High columnar redundancy enables strong compression, allowing more rows per page.

Compressed pages increase cache‑hit rates, keeping hot pages in memory.

Batch processing mode provides superior execution performance over traditional row‑by‑row processing.

2. Physical Layout

The engine creates column store indexes in three steps:

Group rows into Row Groups .

Within each row group, each column forms a Column Segment .

Compress and encode each segment independently.

3. Encoding and Compression

Column store uses two encoding types:

Dictionary‑based encoding : builds a dictionary of unique values and stores indexes to the dictionary, effective for columns with many repeated values.

Value‑based encoding : scales integer or decimal ranges and stores an exponent, ideal for columns with tightly clustered numeric values.

Both encodings are applied by the VertiPaq engine.

03. Column Store Index Syntax

SQL Server 2012 introduced non‑clustered column store indexes. Example creation script:

CREATE NONCLUSTERED COLUMNSTORE INDEX index_name
ON schema_name.table_name (column1, column2, ...)
WITH (DROP_EXISTING = OFF, MAXDOP = 0)
ON partition_scheme_name(column_name) | filegroup_name;

After creation, the base table becomes read‑only. To modify data, disable the index, perform DML, then rebuild:

ALTER INDEX mycolumnstoreindex ON mytable DISABLE;
-- perform updates on mytable --
ALTER INDEX mycolumnstoreindex ON mytable REBUILD;

Because building or rebuilding a column store index is I/O‑intensive, it should be performed during low‑usage periods.

04. Space Usage

Each column segment occupies its own storage; the total index size is the sum of all segment sizes. The system view sys.column_store_segments reports per‑segment row counts and on‑disk size.

SELECT i.object_id,
       OBJECT_NAME(i.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       COL_NAME(i.object_id, ic.column_id) AS index_column_name,
       SUM(s.row_count) AS row_count,
       SUM(s.on_disk_size) / 1024 / 1024 AS on_disk_size_mb
FROM sys.column_store_segments s
JOIN sys.partitions p ON s.partition_id = p.partition_id
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id AND s.column_id = ic.index_column_id
GROUP BY i.object_id, i.index_id, i.name, i.type_desc, ic.column_id
ORDER BY i.object_id, i.name, index_column_name;

Segments are typically small, resulting in low I/O and memory consumption, especially when combined with batch processing, making column store indexes highly effective for star‑join aggregation queries in data warehouses.