THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: The Naming of Things


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,
       index_id, memory_consumer_desc
FROM sys.dm_db_xtp_memory_consumers;

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!


Published Friday, May 27, 2016 3:46 PM by Kalen Delaney
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



petterson said:

I want to express my admiration of your writing skill and ability to make readers. I would like to read newer posts and to share my thoughts with you

May 31, 2018 1:52 AM

obat diabetes said:

August 1, 2018 11:14 PM

obat bronkitis anak secara alami said:

August 7, 2018 7:47 PM

obat sinusitis said:

August 10, 2018 7:21 PM

masker wajah alami untuk menghilangkan jerawat said: Masker wajah alami untuk menghilangkan jerawat Obat liver alami aman dan terbaik Obat herbal sinusitis kronis terbaik Obat bronkitis anak secara alami Obat Kanker Payudara terbaru 2018 Cara meningkatkan nafsu makan secara alami Obat herbal diabetes menurunkan gula darah Obat herbal kusta terbaik Obat benjolan di leher tanpa operasi Obat Herbal Kanker Otak tuntas hingga akar Obat TBC alami aman tanpa efek samping Obat radang amandel sembuh tanpa operasi Obat asam urat alami tanpa efek sampinfg Obat tetes Sariawan secara alami Cara mengobati mata bengkak dengan cepat Obat tradisional kanker lambung terbaik Obat kanker paru paru Obat usus buntu alami tanpa operasi Obat asam lambung naik cara alami

August 20, 2018 8:57 PM

obat herbal koresterol terbaik said:

August 23, 2018 10:56 PM

obat usus buntu alami tanpa operasi said:

August 24, 2018 12:19 AM

obat benjolan di tubuh said:

August 30, 2018 7:39 PM

obat wasir said:

September 4, 2018 8:25 PM

obat katarak said:

September 16, 2018 5:45 PM

obat maag said:

September 17, 2018 7:09 PM

Electrical Engineering Online Assignment Help said:

Those who come to read your article will find lots of helpful and informative tips

September 19, 2018 4:11 AM

Michael said:

Looking  for someone write my assignment  here are Expert assignment helpers  are well efficient and  capable of creating unique assignments for college or university students with online assignment help.

September 26, 2018 11:33 PM

Lucy said:

We aren't most effective living in a knowledge-based totally society but in one which has made that expertise an increasing number of without difficulty handy everywhere, every time in the world.

Visit Now,

November 30, 2018 12:22 AM

daniel smith said:

Looking  for someone write my assignment  here are Expert assignment helpers  are well efficient and  capable.

January 8, 2019 11:49 PM

jon bernthal punisher vest said:

the person who create this post it was a great human..thanks for shared this

January 9, 2019 11:33 PM

james bond coat said:

Those who come to read your article will find lots of helpful and informative tips. thanks for sharing with us..

January 14, 2019 3:31 AM

David Millar said:

its a fine distinction you want to make here I guess I kn ow I admire someone if I. which would give me ideas of how to express my self someone whose very being.

January 23, 2019 4:14 AM

Leena Dany said:

I am happy to visit your distinguished way of writing in this post. Now it is easy to understand and implement the concept for me. Keep writing these types of posts.

January 26, 2019 5:04 AM

The Punisher Thomas Leather Coat said:

I am always searching online for articles that can help me and this is one of them. This is an interesting and so well maintained blog. Whole article is too good and well written.

February 2, 2019 3:25 AM

Men Biker Jacket said:

Wow great blog I think my friend was right, My friend suggest me to visit your blog to get useful information and now I’m also satisfied he was right now I’m your visitor. good work sir keep it up.

February 7, 2019 3:43 AM

David Millar said:

This term has been around for. you drag a piece of bait behind the boat to see what will bite. In message site like Y!A and post something you KNOW will get everyone. you sit back and laugh. The person who posts the original anger.

March 15, 2019 11:03 AM

zaheer said:

Wow extraordinary blog I think my companion was correct, My companion propose me to visit your blog to get valuable data and now I'm additionally fulfilled he was correct currently I'm your guest. great work sir keep it up.

visit my site:

April 12, 2019 3:44 AM

womens soft leather jacket said:

Attractive information on your blog, thank you for taking the time and share with us. Amazing insight you have on this, it’s nice to find a website the details so much information about different artists.


April 16, 2019 4:45 AM

Mike Rooney said:

Are you typing, Do my assignment online on several search engines.

March 23, 2020 11:27 PM

leeqeen said:

We are providing a lot of good services. With the advertising search engine, you will benefit from using our services. and much more benefit from my company.Thanks

March 24, 2020 3:29 AM

Mike Rooney said:

April 2, 2020 1:32 AM

Emma said:

I am glad that i came to see this, really loved it. Thank you for sharing it. I have bookmarked your page, excited to see more of it!

April 3, 2020 9:11 AM

25 minute time said:

Yes Great Article,Thanks for sharing for this informative blog post and i really appreciate your work keep it up.

April 8, 2020 3:58 PM

what does ceo stand for said:

This is Awesome I really happy to read this blog post. Thanks for this lovely blog. I really appreciate you keep blogging.

April 28, 2020 11:48 PM

projectfreetv said:

Your post amazing and very helpful for us I was impressed with your blog. Thanks for sharing such informative blog. <a href="">projectfreetv</a>

April 29, 2020 12:52 AM

David said:

Nice information.<a href="">8 ball pool mod apk</a>

May 11, 2020 1:21 AM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement