Possible to take an non in memory table from an ERP and make it in memory

Jonathan Brotto 286 Reputation points
2020-12-16T17:09:53.307+00:00

We have SQL server 2014 and would like to test on our test database the in memory functionality of SQL 2014. Is there a way to convert a table?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,690 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 100.9K Reputation points MVP
    2020-12-16T22:17:52.823+00:00

    You cannot "convert" the table, but you can move the existing table to a different schema (for instance the guest schema) and then create an in-memory version of that table and copy data over.You would also have to transfer indexes as needed. On SQL 2014, there is no support for foreign keys on in-memory tables.

    Beware that once you have created an in-memory table, you can never remove the in-memory filegroup, even if you drop all in-memory tables.


1 additional answer

Sort by: Most helpful
  1. David Browne 111 Reputation points Microsoft Employee
    2020-12-16T20:02:38.273+00:00

    Use the "Memory Optimization Advisor" in SSMS.

    The memory-optimization advisor allows you to:

    Identify any features used in a disk-based table that are not supported for memory-optimized tables.

    Migrate a table and data to memory-optimized (if there are no unsupported features).

    For information about migration methodologies, see In-Memory OLTP - Common Workload Patterns and Migration Considerations.

    https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/memory-optimization-advisor