Home > Workload Solutions > SQL Server > Guides > Microsoft SQL 2019 on Intel Optane Persistent Memory (PMem) Using Dell PowerEdge Servers > Create memory-optimized tables
To work with memory-optimized tables in SQL Server, follow these steps based on the requirements and workloads.
/*To create memory filegroup and add a file to it*/
ALTER DATABASE [memdb]
ADD FILEGROUP [memdb_memfg] CONTAINS MEMORY_OPTIMIZED_DATA;
GO
ALTER DATABASE [memdb]
ADD FILE (NAME='memdb _memfg_dir', FILENAME='/mnt/memfg/memdb_memfg_dir')
TO FILEGROUP [memdb _memfg];
GO
Based on the structure of the databases, it can be beneficial to move certain tables and structures into memory. Transaction Performance Analysis reports, workload analysis, and other factors determine this decision. See Determining if a Table or Stored Procedure Should Be Ported to In-Memory OLTP.
Microsoft SQL Server’s memory optimization advisor can assist with the migration process. We suggest using the advisor to assess the relevant warnings and changes that are needed to the indexes. Then, using the script generator, a script for the migration process can be generated, edited, and run to perform the migration.
For details, see Memory Optimization Advisor.
/* create memory optimized table with both schema and data durability */
CREATE TABLE [dbo].[customer]
(
[c_custkey] [bigint] NOT NULL,
[c_mktsegment] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[c_nationkey] [int] NULL,
[c_name] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[c_address] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[c_phone] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[c_acctbal] [money] NULL,
[c_comment] [varchar](118) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
INDEX [cust_cs] CLUSTERED COLUMNSTORE WITH (COMPRESSION_DELAY = 0),
INDEX [customer_pk] UNIQUE NONCLUSTERED ( [c_custkey] ASC ),
CONSTRAINT [customer-o_primaryKey] PRIMARY KEY NONCLUSTERED ( [c_custkey] ASC )
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO
/* Then, insert data from the disk-based table into memory optimized table: */
INSERT INTO [dbo].[customer]
Select * from [dbo].[customer-diskBased]
GO