Indexes in SQL Server 2014's Memory Optimized Tables
SQL Server 2014’s Memory Optimized Tables handle indexes very differently than traditional tables. First and foremost, you must have at least one index and cannot have more than eight indexes.
The one required index is used to organize the data in memory. Unlike a traditional table, there memory optimized tables do not support storing data in an unordered heap. This one index contains the primary key, which is the only unique column allowed for the table. Furthermore, that primary key cannot be an identity column.
In fact, identity columns are completely prohibited. This is most likely a limitation needed to support the lock-free writes used by the structure.
The other seven indexes are used almost exclusively for assisting in joins and order by operations. As mentioned before, you cannot add additional unique indexes. Nor can you use the indexes to enforce foreign key constraints.
You cannot work-around these limitation using triggers either, as they are not supported by memory optimized tables.
Finally, check constraints are not allowed. This means practically all data integrity concerns must be pushed up into the stored procedure or application tier.
But wait, there’s more. You also can’t place indexes on nullable columns. Nor can you use filtered indexes; each index must reference every row.
Rows in memory optimized tables are not arranged in pages. Instead, they are scattered in memory. The only way to access them is via the indexes, hence the reason at least one index is required.
These indexes are not your traditional B-Tree. Instead it uses a hash index with a fixed number of buckets. Ideally each bucket will only have one row in it, so when creating the index you should specify the number of rows you expect the table to hold. Be careful though, over-estimating the number of rows can be quite wasteful when it comes to memory.
A planned alternative to this are range indexes. Though not available yet, range indexes are expected to handle an unknown number of rows better.
We will continue this series tomorrow by looking at Natively Compiled Queries.