11. Columnstore, In-Memory OLTP and other storage options

Columnstore indexes store data by column in compressed segments (rowgroups of ~1 million rows), with batch mode execution processing ~900 rows at a time. Huge gains (10× compression, 10–100× faster) for analytics and aggregations over millions of rows; less suited to single-row lookups and frequent small updates (delta store + tuple mover handle inserts).

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales ON dbo.FactSales;            -- data warehouse fact table
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders ON dbo.Orders(OrderDate, Total, Status);   -- real-time analytics on OLTP

In-Memory OLTP (Hekaton): memory-optimised tables with lock- and latch-free structures (hash and range indexes), optimistic multi-versioning and natively compiled stored procedures — for extreme insert/update rates (session state, queues, IoT ingestion). Durable (SCHEMA_AND_DATA) or not.

Partitioning: split a large table by a column (usually date) into partitions on filegroups — partition elimination, fast archiving with SWITCH, per-partition index maintenance. It's mainly a manageability feature; queries without the partition key don't get faster.

Compression: row and page compression reduce I/O and memory at some CPU cost.