SQL 2016 - I have 2 tables, Foo & Bar - Bar references Foo. I'd like to turn Foo into an in-memory optimized table, however as Bar references this, I can't do it.

I also can't make Bar in-memory due to its dependence on Foo.

Seems like I need to do this to both tables at the same time - is this possible without recreating both tables from scratch?


Seems like I need to do this to both tables at the same time

Correct. If you try to recreate the FK after migrating one table you will get:

Msg 10778, Level 16, State 0, Line 20
Foreign key relationships between memory optimized tables and non-memory optimized tables are not supported.

is this possible without recreating both tables from scratch?

Migrating to in-memory tables always requires recreating the tables from scratch. SSMS will rename your old tables, create the new in-memory tables, load them from your old tables, and drop your old tables.

  • Thank you, David - doesn't seem possible from the SMSS GUI then. – Steven Sproat Aug 16 '17 at 13:46
  • Hi David, how would this work in production without a change to our application code? Since RAM tables cannot be renamed, we'd have to rename the existing tables (breaking the existing application), recreate the RAM tables with the correct name - I'm struggling to see a way of doing this without causing errors in the application. – Steven Sproat Aug 16 '17 at 14:31
  • 1
    All the changes would need to happen during application downtime, as creating memory optimized tables in a transaction is not supported. – David Browne - Microsoft Aug 16 '17 at 14:45
  • I feared so - thanks for the responses, David. – Steven Sproat Aug 16 '17 at 15:10
  • 1
    I realize that SQL 2017 is not yet RTM, but when it is, it will support renaming memory-optimized tables and native modules, i.e. procs/functions/triggers: bit.ly/2waYGT6 – NedOtter Aug 19 '17 at 3:50

Finally, I found one solution for foreign Key Table migrate to In-memory table. For that you need to open the disk-based table which contains key, script with drop and create option than put the comment on foreign key syntax than execute it. After that it's like normal table hence, you migrate it into In-Memory table, it'll successfully migrate in. Then re-open table script with Drop and Create option and remove comment than execute it. It'll create foreign key into In-memory table.

Note : Please take backup of all data before processed

I know this tedious process but that's only way I think

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.