0

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?

Improve this question

2 Answers 2

Reset to default
1

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.

Improve this answer
5
  • Thank you, David - doesn't seem possible from the SMSS GUI then.
    – Steven Sproat
    Commented Aug 16, 2017 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
    Commented Aug 16, 2017 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
    Commented Aug 16, 2017 at 14:45
  • I feared so - thanks for the responses, David.
    – Steven Sproat
    Commented Aug 16, 2017 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
    Commented Aug 19, 2017 at 3:50
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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