Index Internals: RowStore, ColumnStore, and NoStore!
summary
Explains precise behaviors and tradeoffs: rowstore = B‑tree leaves contain every data row, columnstore groups ~2^20 (~1,048,576) rows per row group and nonclustered columnstores make the table read‑only, and in‑memory (Hekaton) delivers reported 30–40x speedups toward a 100x goal. Describes which SQL Server versions add features (2012 nonclustered columnstore, 2014 clustered columnstore, 2014 in‑memory), details on hash vs range indexes, index persistence (indexes rebuilt in recovery) and practical limits (max eight in‑memory indexes).
AI-written summary · about
chapters (22)
- 0:00Intro
- 1:00Technical Assistance
- 4:07Kalen Delaney Bio
- 6:30Precon Abstract
- 7:48Session Overview
- 8:24Rowstore Indexes
- 9:51B-Trees
- 12:22What is in the Leaf Level?
- 18:49When to Use: Disk-based B-Tree Indexes
- 24:19Segments and Rowgroups
- 26:42Columnstore Index Basics
- 30:22When to Use: Columnstore Indexes
- 32:51In-memory Indexes (Nostore)
- 35:33Why In-Memory OLTP?
- 43:52Hash Indexes
- 46:43When to Use: In-Memory Indexes Only on memory-optimized tables
- 48:28Comparisons
- 48:59Fragmentation
- 52:19Statistics
- 53:46Maintenance
- 54:52Session Summary Rowstore
- 57:41Like What You've Heard?
related talks
20mColumnstore Index Compression in a Nutshell- 20mColumnstore Index Compression in a Nutshell
27mDatabase In-Memory: Real-time analytics, supercharged
53mIndexing Internals for Developers & DBAs- 53mIndexing Internals for Developers & DBAs
46mKEEPING UP IN A DATA-INTENSIVE WORLD WITH SINGLESTORE
51mNext Level Scalability on SQL Server 2022
50mTime Series with SQL Server 2022