Indexes in SQL Server 2014's Memory Optimized Tables

by Jonathan Allen on Sep 24, 2013 |

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.

Rate this Article


Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread
Community comments

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

General Feedback
Marketing and all content copyright © 2006-2016 C4Media Inc. hosted at Contegix, the best ISP we've ever worked with.
Privacy policy

We notice you're using an ad blocker

We understand why you use ad blockers. However to keep InfoQ free we need your support. InfoQ will not provide your data to third parties without individual opt-in consent. We only work with advertisers relevant to our readers. Please consider whitelisting us.