Creating and using memory optimized table

Introduction

Memory optimized tables allow you to keep the data in memory and return the result fast whenever required. Since the data is stored on RAM so the disk io is 0 for memory optimized tables.

Steps to create memory optimized tables

Step1 

Create a database and add a filegroup and file and set memory optimize data on as below.

CREATE DATABASE imoDB   GO 
-------------------------------------- 
-- create database with a memory-optimized filegroup and a container. 
ALTER DATABASE imoDB   ADD FILEGROUP imoDB_mod CONTAINS MEMORY_OPTIMIZED_DATA   ALTER DATABASE imoDB   ADD FILE (name='memoltp_mod1', filename='c:\data\imoltp_mod1') TO FILEGROUP imoDB_mod   ALTER DATABASE imoDB   SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON  GO

Step 2 

Using above database you can use the below syntax to create a memory optimized table.

USE imoDB

GO

CREATE TABLE EMPLOYEE

(

EMPID INT IDENTITY(1,1),

EMPNAME NVARCHAR(255),

[ADDRESS] NVARCHAR(400)

) WITH (MEMORY_OPTIMIZED=ON)

Step 3 

Create natively compiled stored to insert and retrieve data.

CREATE PROCEDURE ADDEmployees

(

@number INT

)

with native_compilation,

         schemabinding,

         execute as owner

as

begin atomic

with (transaction isolation level = snapshot,

          language = N'us_english')

DECLARE @EmpID INT = 1

WHILE @EmpID <= @number

BEGIN

INSERT INTO EMPLOYEE VALUES('Employee_'+ CAST(@EmpID AS NVARCHAR(10)),'Address_'+CAST(@EmpID AS NVARCHAR(10)))

SET @EmpID = @EmpID + 1

END

To retrieve data

CREATE PROCEDURE ADDEmployees

(

@EmpID INT

)

with native_compilation,

         schemabinding,

         execute as owner

as

begin atomic

with (transaction isolation level = snapshot,

          language = N'us_english')

SELECT * FROM EMPLOYEE WHERE EMPID = @EmpID

end;