SQL Server Portal

In my earlier article, I wrote a solution to create memory optimized table in a new database. But the question is how to create memory optimized table in an existing database. Let me explain it step by step.

Step 1 :
In this step we need to create a simple database to demonstrate. Skip this step if you already have a database but make sure its compatibility level is 110.

USE master;
GO
CREATE DATABASE test
ON
( NAME = Sales_dat,
FILENAME = 'C:\DATA\test.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
FILENAME = 'C:\DATA\test_log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
GO
--OUTPUT

Step 2 :
Create a memory optimized table.

USE Test
GO
CREATE TABLE tbl_sample_Memory_table
(
[ID] integer not null primary key nonclustered hash
with (bucket_count = 1024),
[Name] varchar(200) not null,
) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Msg 41337, Level 16, State 0, Line 3
The MEMORY_OPTIMIZED_DATA filegroup does not exist or is empty. Memory optimized tables cannot be created for database until it has one MEMORY_OPTIMIZED_DATA filegroup that is not empty.

Ooopps…… I am unable to create memory optimized table in TEST database (existing database).

So, what’s the remedy for it ?

Step 3 :
Create a file group, add file in it containing memory optimized data option. Given below is the script.
Do not forget to change the path in the script.

USE [master]
GO
ALTER DATABASE [test] ADD  FILEGROUP [test_fg]
CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE [test] ADD FILE
( NAME = N'test_fg', FILENAME = N'C:\DATA\test_fg.ndf')
TO FILEGROUP [test_fg]
GO
USE [test]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups
WHERE is_default=1 AND name = N'test_fg')
ALTER DATABASE [test] MODIFY FILEGROUP [test_fg] DEFAULT
GO
--OUTPUT

Step 4 :
Once the file group is created, just create the memory optimized table and this time it will be created successfully.

USE Test
GO
CREATE TABLE tbl_sample_Memory_table
(
[ID] integer not null primary key nonclustered hash
with (bucket_count = 1024),
[Name] varchar(200) not null,
) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Command(s) completed successfully.

Step 5 :
Now, browse the table and check whether it is created or not.

USE Test
GO
SELECT * FROM tbl_sample_Memory_table
--OUTPUT

Conclusion :
Whenever you create a memory optimized table, you must consider two things before creating it.
1- The database must have the compatibility level 110
2- Database must have a file group having CONTAINS MEMORY_OPTIMIZED_DATA