How to Fix Error Msg 41337, Level 16, State 100 in Create Memory Optimize table in SQL Server

Question

I want to use a memory optimize table and I got below error - how can I fix it? Any idea will be helpful.

Msg 41337, Level 16, State 100, Line 1
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.

Code:

CREATE TABLE dbo.Account
(  
    AccountID INT IDENTITY(1, 1) PRIMARY KEY NONCLUSTERED
) WITH (MEMORY_OPTIMIZED=ON)

Answer 1

you must create a file Group first :

Create FileGroup :

imoltp is a sampledatabase

ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod
    CONTAINS MEMORY_OPTIMIZED_DATA;

Add DatabaseFile To Your File Group :

ALTER DATABASE imoltp ADD FILE (
    name='imoltp_mod1', filename='c:dataimoltp_mod1')
    TO FILEGROUP imoltp_mod;

Create Memory Optimize Table:

CREATE TABLE dbo.Account(   
    AccountID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED
    ) WITH (MEMORY_OPTIMIZED=ON)

take a look at this for get more Info : https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/creating-a-memory-optimized-table-and-a-natively-compiled-stored-procedure?view=sql-server-ver15 for more detail