Which Indexes are Disk Based in SQL Server?

I was looking through some terms in SQL Server documentation the other day, thinking about what it’s like to learn about SQL Server’s indexes when you’re new to the field. I jotted down a note: B-tree = Rowstore = Disk Based.

And then I realized that’s not quite right.

Not all disk based indexes are traditional clustered and nonclustered indexes. Columnstore indexes are also disk based. Updatable Columnstore indexes use special rowstore B-trees behind the scenes. And Books Online says “rowstore” also refers to Memory-Optimized tables.

Here’s a drawing that maps out what is disk based vs in-memory, followed by a list with links to some of the details.

Words are Hard! Show me a Diagram

]

Venn diagram or practical joke? You decide!

Disk Based

• Rowstore - Data stored in row format on 8KB pages
  • Heaps (table without a clustered index)
  • B-trees: Clustered Indexes, Nonclustered Indexes, Filtered Indexes, Indexed Views
• Columnstore Indexes - Data stored in column format on 8KB LOB pages
  • Clustered Columnstore, Nonclustered Columnstore, Filtered Nonclustered Columnstore
  • Fine print: Updatable Columnstore indexes secretly use rowstore btrees behind the scenes: “deltastore” and “delete bitmap”. (That’s not pictured in the Venn diagram above, you’ll have to imagineer it in.)

Notes on mixing and matching disk based rowstore and columnstore technologies…

1. Starting in SQL Server 2016, you can create one rowstore nonclustered index on a Clustered Columnstore table, which you might do to enforce a unique constraint or for seek query performance. Read more here.
2. Nonclustered Columnstore indexes can be created on rowstore Clustered Indexes or heaps, although the table only becomes updatable in SQL Server 2016. Read more on Columnstore features by version here.

In-Memory

• In-Memory OLTP
  • B-trees: Memory-optimized Nonclustered Indexes (These B-trees are different than on-disk rowstore B-trees in that they exist only in memory, contain memory addresses instead of key values, and have no fixed pages. Read more here.)
  • Hash Indexes (A specialized index only for memory-optimized tables which “bucketizes” values. Read more here.)

For durable in-memory tables, data is written to data and delta files so it’s not lost when the database goes offline. This is very different from “disk based” tables because user transactions do NOT read from the data and delta files. Read more here.

Both Disk Based AND In-Memory (Special Case)

• Columnstore Clustered Index on a Memory-optimized table
  • This is persisted to disk but is ALSO required to stay in memory for access. See Sunil Agarwal’s post for diagrams.