Create memory Optimized table in SQL 2016

• In memory OLTP Enhancement

Memory optimized tables speed up transnational workloads, high concurrency and too many latches by moving data from disk-based to memory optimized tables.

It helps to improve the performance of DW staging by using non-durable, memory-optimized tables as staging tables.

Features which Memory optimized tables support/not support

Support	Not Support
Check Constraints	Replication
Unique Constraints	Database Snapshots
Foreign Keys : As long as foreign key reference to a “primary key”	Remove “ON” clause from “create table statement”
Triggers: for insert/update/delete as long as it use “with native Compilation”	Datetimeoffset/ Hierachy id
Columns with large object types(LOB): Varchar(max), Nvarchar(max), Binary(max)	DBCC checkDB / DBCC checktable
Noncluster index: specify primary key nonclustered.	Clustered index
Unique/nullable index key columns	Create index: Must be specified with create statement or alter table statement.

Scaling Memory Optimized Tables

• Can put more date into memory optimized tables.
• Support bigger workloads.
• Can store upto 2TB of data when you create a durable table by using "Schema_and_data" option in the create table statement.
• Create memory optimized table with row size > 8060 bytes even without a LOB column
• Support parallel scans of all indexes (nonclustered + hash). It is beatifically when you have queries which scan large data sets.

Create Memory Optimized table.

Before creating memory optimized table you should complete below 2 steps.

• Create memory-optimized database file group 
• Create filestream database file.

If you are unable to fulfill above steps before creating memory optimized tables, SQL Server engine will rise an exception "Cannot create memory optimized tables in a database that does not have an online and non-empty MEMORY_OPTIMIZED_DATA filegroup".

Note:

• You can only create one memory-optimized file group per database.
• You can create the file group when you create the database or you can add it later.

There are 2 ways to accomplish it.

1. Using SQL Query statements
2. Using DB interface.

Ok then lets start.

Assumption: Lets assum that you have alreay created the Database.

1.Using SQL Query statements

Create in memory optimized file group

ALTER DATABASE DB NAME ADD FILEGROUP FILEGROUP NAME CONTAINS MEMORY_OPTIMIZED_DATA  

Ex: ALTER DATABASE SQL2016 ADD FILEGROUP imoltp CONTAINS MEMORY_OPTIMIZED_DATA 

Create filestream database file

ALTER DATABASE DB NAME ADD FILE (NAME='NAME OF THE DB FILE', FILENAME='PATH WHERE FILE IS STORED ') TO FILEGROUP FILEGROUP NAME

Ex: ALTER DATABASE SQL2016 ADD FILE (name='imoltp_mod1', filename='C:\Program Files (x86)\SQL2016_IMOLTP') TO FILEGROUP imoltp

Now Crete in memory optimized table with durability to store more than 2tb of data

CREATE TABLE ProductReviewHistory (
ProductID INT IDENTITY PRIMARY KEY NONCLUSTERED,
ProductEnglishDescription NVARCHAR(4000),
ProductReviewDate DATETIME,
ProductSpanishReviewComments NVARCHAR(4000),
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

2. Using DB Interface

Create in memory optimized file group

Create filestream database file

Now Crete in memory optimized table

CREATE TABLE ProductReviewHistory (
ProductID INT IDENTITY PRIMARY KEY NONCLUSTERED,
ProductEnglishDescription NVARCHAR(4000),
ProductReviewDate DATETIME,
ProductSpanishReviewComments NVARCHAR(4000),
) WITH (MEMORY_OPTIMIZED = ON);

Cheers :)

Thilini