As a teacher, and also a student of languages, one issue that’s always fascinating to me is how people choose to shorten a compound, or multi-part, name. This is very problematic when the word is from another language. For example, we use the word latte in English as a short form of caffè latte, but we shorten it to just the part that means ‘milk’, not coffee! My daughter was an exchange student in Spain and told me another example. Her host mother kept asking her if she had a ‘body’. The question was in Spanish, except for the word ‘body’ and my daughter just couldn’t figure out what she was talking about. The woman was confused thinking for sure my daughter should understand since it was an English word! Finally, they figured out the woman was referring to a bodysuit, but shortening it to something that really had no meaning on its own. In English, we have the words grape and grapefruit. I had a friend (a native Chinese speaker) who could just never remember which was which and frequently mentioned grapes when she meant grapefruit! And sometimes she did it the other way around and asked her native-English speaking husband to get her a bag of grapefruits, when she just wanted grapes. These are two very different things! I have more examples, but I’m getting a bit off track here.
In SQL Server, we also have concepts that have compound names that are frequently shortened. For ‘isolation level’, you could just refer to ‘isolation’ and people would probably know what you mean. But if you just said ‘level’, your meaning might not be so obvious.
And what about pairs of terms with different qualifiers, like clustered indexes and nonclustered indexes? If someone just says index, you usually need to know which kind.
And there are other terms which have the same base, and only one of them has a qualifier. And that’s really why I decided to write this blog post. In SQL Server 2005, Microsoft introduced snapshot isolation for read operations. There were two different levels of snapshots. The terms we use are SNAPSHOT and READ COMMITTED SNAPSHOT. As a teacher, I always found this very confusing, as did many of my students. People would say SNAPSHOT, but it was not at all clear if they really meant READ COMMITTED SNAPSHOT. So I started using an additional qualifier, so that both terms would be qualified. I started saying TRANSACTIONAL SNAPSHOT, but that was too cumbersome, so then in class I just started calling it FULL SNAPSHOT. I just wanted both terms to have some qualifier, so if someone just said SNAPSHOT, I could ask which one they meant.
Then in SQL Server 2014, Microsoft introduced In-Memory OLTP, with two new kinds of indexes. One is called NONCLUSTERED HASH, and the other is just plain old NONCLUSTERED. And again, I found this unbalanced. If someone just says NONCLUSTERED, am I sure they’re just not shortening NONCLUSTERED HASH inappropriately? So I started using the word RANGE as the qualifier for the non-HASH index. Then we could talk about NONCLUSTERED RANGE and NONCLUSTERED HASH, or just shorten the names to RANGE and HASH. And when I’m teaching or writing about these indexes, I am always careful to point out that the word RANGE is not part of the syntax, it’s just a descriptor I use.
However, one of the engineers at Microsoft was not happy with the fact that my writings used the term RANGE index. He said it wasn’t part of the spec, so I shouldn’t use it. It would just confuse people. He claimed Microsoft never used that term and I shouldn’t either. I argued. I explained my pedagogical reasons. He finally just shrugged and decided that I probably wasn’t adding any additional obfuscation and grudgingly admitted I might actually be making things clearer. But he wasn’t happy about it. He just didn’t want me using the term RANGE.
Now SQL Server 2016 is coming out. I’m doing research on some of the new storage structures and new metadata. And I’m looking at a DMV called sys.dm_db_xtp_memory_consumers with a column called memory_consumer_desc. And one of the possible values for this column is ‘Range index heap’. I just checked, and the view is also available in SQL Server 2014, but I just never got around to exploring it until now. And if I create a memory-optimized table called OrderDetails2 on a SQL Server 2014 instance, with three RANGE indexes, and then run this query:
SELECT object_name(object_id) as object_name,
I will get these rows of output (among others):
So RANGE INDEX is not just a term I chose to use to make things easier for my students and my readers. Someone else, designing DMVs, also thought it was a useful term to use.
I am just about done upgrading my In-memory OLTP Internals whitepaper for SQL Server 2016 RTM. Once that’s published, you’ll be able to read all the details of the various memory consumers used for memory-optimized tables. Hopefully, you won’t have to wait too long!