Clustered Columnstore Indexes in SQL Server 2014: Columnar Storage for OLAP Databases

The Read-Only Columnstore Limitation

SQL Server 2012 introduced non-clustered columnstore indexes, which significantly accelerated analytics queries. However, tables with these indexes became read-only, requiring administrators to drop the index, update rows, and recreate the index for updates.

The upcoming launch of SQL Server 2014 resolves this by introducing Clustered Columnstore Indexes (CCI).

DB Design Rule: Use Clustered Columnstore Indexes for high-volume data warehouse fact tables. Do not use CCIs for transaction-heavy tables.

The Writable Columnar Table Design

A Clustered Columnstore Index is the primary storage structure for the table. It is writable, organizing data using two elements:

• ◆Rowgroups: Collections of up to 1 million rows compressed into columnar format.
• ◆Deltastore: A temporary, row-oriented table (B-Tree) that captures new inserts quickly.
• ◆Tuple Mover: A background process that compresses rows from the deltastore into columnstore rowgroups once they reach 1,048,576 rows.

Creating a Clustered Columnstore Index

-- Creating a writable clustered columnstore index in SQL Server 2014
CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesFact
ON SalesFact;

By combining columnar storage compression with automated write queues, SQL Server 2014 provides high-performance data warehousing without administrative overhead.