Memory Optimized Tables - can they really be so difficult to maintain? I'm investigating the benefits of upgrading from MS SQL 2012 to 2014.  One of the big selling points of SQL 2014 is the memory optimized tables, which apparently make queries super-fast.

I've found that there are a few limitations on memory optimized tables, such as:


No (max) sized fields
Maximum ~1KB per row
No timestamp fields
No computed columns
No UNIQUE constraints


These all qualify as nuisances, but if I really want to work around them in order to gain the performance benefits, I can make a plan.

The real kicker is the fact that you can't run an ALTER TABLE statement, and you have to go through this rigmarole every time you so much as add a field to the INCLUDE list of an index.  Moreover, it appears that you have to shut users out of the system in order to make any schema changes to MO tables on the live DB.  

I find this totally outrageous, to the extent that I actually cannot believe that Microsoft could have invested so much development capital into this feature, and left it so impractical to maintain.  This leads me to the conclusion that I must have gotten the wrong end of the stick; I must have misunderstood something about memory-optimized tables that has led me to believe that it is far more difficult to maintain them than it actually is.

So, what have I misunderstood?  Have you used MO tables?  Is there some kind of secret switch or process that makes them practical to use and maintain?
No, in-memory really is this unpolished.  If you are familiar with Agile you will know the concept of "minimal shippable product"; in-memory is that.  I get the feeling that MS needed a response to SAP's Hana and its ilk.  This is what they could get debugged in the timeframe for a 2014 release.

As with anything else in-memory has costs and benefits associated with it.  The major benefit is the throughput that can be achieved.  One of the costs is the overhead for change management, as you mentioned.  This doesn't make it a useless product, in my opinion, it just reduces the number of cases where it will provide net benefit.  Just as columnstore indexes are now updatable and indexes can be filtered I have no doubt that the functionality of in-memory will improve over coming releases.



SQL Server 2016 is now generally available. Just as I supposed, In-Memory OLTP has received a number of enhancements. Most of the changes implement functionality that traditional tables have enjoyed for some time. My guess is that future features will be released at the same time for both in-memory and traditional tables. Temporal tables is a case-in-point. New in this version it is supported by both In-Memory and disk-based tables.