Indexes On Disk

Kendra Little has a great diagram showing which indexes are disk-based and which are memory-resident:

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.

If you’re new to indexing, this picture will save you some learning time.

Related Posts

Naming Indexes

Monica Rathbun hits one of my hobby horses: As you can see from above, none of the names gave a complete indication of what the index encompassed. The first one did indicate it was a Non Clustered Index so that was good, but includes the date which to me is not needed. At least I knew […]

Read More

Disabled Indexes And Missing Index Recommendations

Brent Ozar asks (and answers) an interesting question: Do Disabled Indexes Affect Missing Index Recommendations? I’m so glad you asked! Let’s take a look. Read on to learn if Betteridge’s law of headlines holds.

Read More

Categories

July 2016
MTWTFSS
« Jun Aug »
 123
45678910
11121314151617
18192021222324
25262728293031