SQL Server Interview Question #236

What is a columnstore index?

Advanced Indexing Senior Advanced

Detailed Explanation

A columnstore index stores data by column rather than primarily by row, using compressed column segments and batch-oriented processing. It is designed especially for analytical workloads that scan and aggregate large numbers of rows while reading relatively few columns.

SQL Server supports clustered and nonclustered columnstore indexes. Columnstore can dramatically improve data warehouse and reporting workloads, while modern versions also support operational analytics scenarios.

It should be selected according to workload characteristics rather than used as a replacement for every rowstore index.

Code Example

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON dbo.FactSales;