Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
Making changes to a “live” system of the scale that memory optimized tables where designed for it often not allowed anyway due to the risk control systems your “boss’s boss’s boss” has put in place. –  Ian Ringrose Sep 1 at 13:29
You are spoiled by the features of a good DBMS along with a mature engine. Come on, I mean, some of us have to live with MySQL. Other than that, if it is that much of a deal for your use case, wait for 2014 R2 - SQL Server's feature history shows that many of the initial limitations are removed in subsequent releases. –  syneticon-dj Sep 1 at 20:08
@syneticon-dj: "... some of us have to live with MySQL." (Laughs with schadenfreude) :) –  Shaul Behr Sep 2 at 6:45

2 Answers 2

up vote 8 down vote accepted

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 inexes can be filtered I have no doubt that the functionality of in-memory will improve over coming releases.

share|improve this answer
Ugh. That's not what I was hoping to hear, but it sounds about right. –  Shaul Behr Sep 1 at 10:21

One of the problems with new technology - especially a V1 release that has been disclosed quite loudly as not feature-complete - is that everyone jumps on the bandwagon and assumes that it is a perfect fit for every workload. It's not. Hekaton's sweet spot is OLTP workloads under 256 GB with a lot of point lookups on 2-4 sockets. Does this match your workload?

Many of the limitations have to do with in-memory tables combined with natively compiled procedures. You can of course bypass some of these limitations by using in-memory tables but not using natively compiled procedures, or at least not exclusively.

Obviously you need to test if the performance gain is substantial in your environment, and if it is, whether the trade-offs are worth it. If you are getting great performance gains out of in-memory tables, I'm not sure why you're worried about how much maintenance you're going to perform on INCLUDE columns. Your in-memory indexes are by definition covering. These should only really be helpful for avoiding lookups on range or full scans of traditional non-clustered indexes, and these operations aren't really supposed to be happening in in-memory tables (again, you should profile your workload and see which operations improve and which don't - it's not all win-win). How often do you muck with INCLUDE columns on your indexes today?

Basically, if it's not worth it for you yet in its V1 form, don't use it. That's not a question we can answer for you, except to tell you that plenty of customers are willing to live with the limitations, and are using the feature to great benefit in spite of them.

share|improve this answer
I was using the "include" columns as an example of a trivial change that I might have to make, but now I have learned from you that this is not a good example. What's more relevant is, for example, adding new nullable columns, which is a very non-disruptive action in a conventional table, but will be very onerous with MO tables. Being that the system we're working on is constantly expanding (our feature requests are competing in number with the bug reports), this is likely to be a killer for us. –  Shaul Behr Sep 1 at 15:54
@shaul ok, so don't use it. Or only put stable tables in memory. Or consider a different design where you're constantly adding columns (EAV). As it is, I think you're just ranting that this tech is not for you. I have kids so I'm not complaining that a Porsche Cayman S is not practical for me - or at least not as a daily driver. Maybe I could use it on the weekends (just like maybe you could use in-memory OLTP for parts of your schema, but not everything). The fact that you have requirements that are not that common, and that conflict with the V1 features, is not Microsoft's fault. –  Aaron Bertrand Sep 1 at 16:39
Aaron, what's EAV? –  Shaul Behr Sep 1 at 18:06
Thanks, that was interesting reading. –  Shaul Behr Sep 2 at 6:43

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.