3

If two tables have the same schema but one is "normal" and the other is memory optimized, if I research on how indexes should be for the "normal", can I do a direct extrapolation for the memory optimized table?

Couldn't find anything conclusive so I really appreciate links, topics and so on.

7

Since the architecture of these two types of tables and indexes is completely different, I would say the answer would be "no". If the table needs to be optimized for point lookups, you should use a HASH index, but that could cause you problems, because if the key is comprised of multiple columns - and you only supply the leading column - you'll get a table scan instead of a seek. In general, it's best to use RANGE indexes, and then only when you can prove you need a HASH index, to possibly deploy that.

Another area is columnstore indexes, which are radically different between the two table types. See my post here: Clustered columnstore: on-disk vs. in-mem.

There is a lot to consider before deploying the In-Memory OLTP feature. I have blogged almost exclusively on this topic for the last two years. I strongly advise you to read my posts....

  • 1
    Another question you should ask is: "what bottlenecks does my database have that are solved by In-Memory OLTP?". There's always a trade-off when using a feature like this, and there are a lot of hidden gotchas. – NedOtter Jul 2 '18 at 5:57
  • Thank you NedOtter, let me take a look at your posts and see if this helps before marking it as the answer or ask anything further. I made my question generic on purpose because i want to understand things before asking for a specific implementation/solution, but in my case i can say that I was pointed to have these two tables. – FEST Jul 2 '18 at 7:43
  • In-Memory OLTP is a study unto itself. One should not deploy it without fully understanding all of the possible implications. We can follow each other on twitter, so we could DM if you have further questions. – NedOtter Jul 2 '18 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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