Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.

Recently, I was working for a client, and I was supposed to create an in-memory table for the performance perspective. However, I got this error when I tried to create the in-memory table. You can see the same error in the screenshot below.

Check out How To Create A Table Valued Function In SQL Server

Solution

I followed the below steps to fix this issue.

1. The first step is to create a file Group using the below query.

ALTER DATABASE Test ADD FILEGROUP Test_mod
    CONTAINS MEMORY_OPTIMIZED_DATA;

2. Now, we can use the below query to add DatabaseFile to your file group.

ALTER DATABASE Test ADD FILE (
    name='Test_mod', filename='c:\Data\Test_mod')
    TO FILEGROUP Test_mod;

After that, I executed the same SQL query again, and this time, the query was executed successfully, as shown in the screenshot below.

Conclusion

To fix this error, we need to create a file Group and add DatabaseFile to your file group, as mentioned in this article.

You may also like following the articles below.

• Saving Changes Is Not Permitted SQL Server
• An Explicit Value For The Identity Column In Table
• Incorrect Syntax Near The Keyword ‘Order’
• Cannot set SYSTEM_VERSIONING to ON when SYSTEM_TIME period is not defined.