SQL Server 2014 – How to Create Memory Optimized table

In eariler post, you have learned how to use In-Memory OLTP Feature of SQL Server 2014 for your database. Here we will learn how to create memory optimized table in SQL Server 2014. Creating Memory is very simple similar to normal table.

How to Create Memory Optimized table In Memory OLTP ?

We will follow the step by step approach to Create Memory Optimized table.

1. Connect to SQL Server 2014 Instance using SQL Server Management Studio, Select your database

2. Under your database, Right click on the “Tables” node and Select “New” from list.

3. Once you move your mouse on “New” and Click on Memory Optimized Table

Note this memory option is only available on databases that have a Memory-Optimized file group

Syntax for creating In-memory table and Stored procedure

Normal Disk based table and Stored procedure

USE Testdb

CREATE TABLE NotInMemory
(Code int not null identity(1,1),
Site varchar(100)
)

CREATE PROCEDURE Insert_SpNotInMemory_tbl @Site varchar
AS
BEGIN

DECLARE @count INT

SET @count = 1

WHILE (@count < 10000)
BEGIN
INSERT INTO NotInMemory @Site
END

SET @count = @count + 1

END

Memmory optimized table after migration

USE InMemoryDB

CREATE TABLE InMemory
(Code int not null,
Site varchar(100)
) WITH (MEMORY_OPTIMIZED=ON)

CREATE SEQUENCE sq_count AS INT START WITH 1 INCREMENT BY 1

CREATE PROCEDURE Insert_SpInMemory_tbl @Site varchar
AS
BEGIN
DECLARE @count INT = NEXT VALUE FOR sq_count
WHILE (@count < 10000)
BEGIN
INSERT INTO InMemory VALUES (@count,@Site)
END
END

Identity data type is not supported by optimized table, There is list
of datatype and features which does not work for optimized tables

Check out this post to know the limitions of optimized tables in sql server 2014
SQL Server 2014 – Limition of Memory optimized tables

Leave a Reply

  

  

  


6 − = 0