Hekaton Part 5: Introduction to Indexes in "In Memory Tables"

After a short little break on Hekaton series, we are now back with the 5th post.

Two new index types have been introduced. They are

1) Hash Index -  Based on Hash algorithm - useful for equality searches
2) Range Index - sometimes referred as Non clustered Index in Memory tables - Similar to traditional Non clustered indexes and useful for range query searches

Indexes on "In Memory" tables are never physically stored in the disk. They are always in purely memory structures. Indexes are re created and loaded into the memory every time database is restarted or when the database restored.

Besides providing the traditional performance improvements, Indexes in " in memory table" another major role. Indexes are actually the chain or the link which holds the entire table together. In other words, each row in the table is linked to the next row using indexes. So, Indexes are a must for in Memory tables. Just to add on

1) On a Schema and Data in memory table ( table which persists on disk ) , primary key is a must. Primary key can be enforced by hash or range index

2) On a schema table ( the table doesn't retain the data after restarts. Only structure is persistent ), there should be atleast one index

 Please note that existing concept of cluster index is not present in "In Memory tables". Also, as they are purely stored in Memory, impact of fragmentation, page splits are bare minimum or non existent in "In memory" tables.

Intro to indexing done. Next post will deal with hash indexes in detail.