BT

New Early adopter or innovator? InfoQ has been working on some new features for you. Learn more

Indexes in SQL Server 2014's Memory Optimized Tables

| by Jonathan Allen on Sep 24, 2013. Estimated reading time: 1 minute |

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.

Internals

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

Adoption Stage
Style

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

Discuss

Login to InfoQ to interact with what matters most to you.


Recover your password...

Follow

Follow your favorite topics and editors

Quick overview of most important highlights in the industry and on the site.

Like

More signal, less noise

Build your own feed by choosing topics you want to read about and editors you want to hear from.

Notifications

Stay up-to-date

Set up your notifications and dont miss out on content that matters to you

BT