Part 156: SQL Server Columnstore Indexes: Clustered vs Non-Clustered

Transforming row-based tables into ultra-compressed columnar powerhouses for aggregation.

1. Rowstore vs Columnstore Storage

Rowstore (Standard B-Tree):
[Row 1: Date | Product | Customer | Amount]
[Row 2: Date | Product | Customer | Amount]

Columnstore:
[Date Column: Row 1, Row 2, Row 3 ... Row 1M] -> 10x Compressed
[Amount Column: Row 1, Row 2, Row 3 ... Row 1M] -> 10x Compressed

When calculating SUM(Amount), Columnstore reads only the Amount column pages from disk, completely skipping Date, Product, and Customer!

2. Clustered vs Non-Clustered Columnstore

• Clustered Columnstore Index (CCI): The entire physical storage of the table is organized into compressed column rowgroups. Ideal for Fact tables in Data Warehouses.
• Non-Clustered Columnstore Index (NCCI): A secondary columnar copy over an existing rowstore table. Enables Real-Time Operational Analytics (HTAP) without offloading to a separate warehouse!

-- Creating a Clustered Columnstore Index on a Fact Table
CREATE CLUSTERED COLUMNSTORE INDEX cci_fact_sales 
ON dbo.FactSales;

-- Real-time analytics index on OLTP orders table
CREATE NONCLUSTERED COLUMNSTORE INDEX ncci_live_analytics 
ON dbo.Orders (OrderDate, CustomerId, TotalAmount);

3. Batch Mode Processing

Columnstore leverages modern CPU SIMD instructions: - Instead of processing rows one by one (Row Mode), the query engine processes batches of up to 900 rows simultaneously in CPU registers.

Key Takeaways

• Columnstore delivers typical compression ratios of 5x–10x and aggregation query speedups of 10x–100x.
• Perfect for million+ row reporting tables and business intelligence workloads.

In Part 157, we explore Oracle SQL & PL/SQL Features: Packages & Fast Materialized Views!