A while back I had written a post about why I’m excited about Hekaton (now called ‘In-Memory OLTP’ – not sure why, Hekaton is much more fun to say), and while I am certainly still excited about this new feature, it does have some significant barriers to entry. Here’s a quick hit list of a few that stood out to me.
No Foreign Key Support
Planning on putting that child table in memory? Be prepared to cut all ties with its parent.
No Check Constraint Support
If you need to enforce a specific set of values for a column, you’re also out of luck.
No Schema Changes
If you’re planning to make schema changes to your in-memory table, you’ll need to get comfortable with the drop and create statements. Dropping and recreating a table is the only way you’ll be able to do anything you’d typically use an ‘ALTER’ statement for, and also the only way you’ll be able to add indexes.
512 GB Maximum Size
Some may argue that this isn’t that big of a barrier, since there aren’t a ton of people out there with enough memory in their servers to store a 512GB table anyway, but it’s worth mentioning. Whatever your table size, it’s important to realize that the required space in memory is double that of what is required on disk, due to MVCC (multi-version concurrency control).
In-Memory OLTP is definitely a step in the right direction and I’m looking forward to seeing Microsoft continue to build upon the technology. The current use cases for it seem limited, however. The most likely scenario I see myself using it for would be a data warehouse table that is getting re-loaded on a nightly basis. If you’re using In-Memory OLTP today, please comment. I’d love to hear about how others are using it.
A complete list of features that aren’t supported can be found here.