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

Index Design When Handling Sorts

Erik Darling walks us through some of the nuance of index deisgn: When tuning queries that need to sort large amounts of data, sometimes it makes sense to stick the ordering elements as the leading key column(s) in your index. This allows SQL Server to easily sort your data by that column, and then access […]

Read More

How DynamoDB Indexing Works

Shubham Agarwal explains how indexing works within DynamoDB: Global secondary index in DynamoDb – An index with a partition key and a sort key that can be different from the base table. A global secondary index is very helpful when you need to query your data without primary key.  The primary key of a global secondary […]

Read More

Categories

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