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:
- Maximum ~1KB per row
- No computed columns
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?