Development

Getting started with In Memory OLTP in SQL Server 2016 – Part 1

In Memory OLTP (Hekaton) introduced in SQL Server 2014 is a new database engine component which is optimized for OLTP workloads to achieve the performance by storing data in memory and accessing data from memory. Memory optimized tables can be accessed using Transact –SQL. When memory optimized tables are accessed all data is stored into the memory.

Today, I will tell you how to create a memory optimized tables.

Let’s first create a database to stored memory optimized tables. Database that contain memory optimized tables must have MEMORY_OPTIMIZED_DATA filegroup. Syntax to create filegroup is same as creating regular filegroup only difference is that here we need to specify option CONTAINS MEMORY_OPTIMIZED_DATA.

–Create a database

IF EXISTS (SELECT * FROM sys.databases WHERE name=’InMemOLTP’)

DROP DATABASE InMemOLTP;

GO

CREATE DATABASE InMemOLTP

ON

PRIMARY(NAME = [InMemOLTP_data],

FILENAME = ‘I:\453491\InMemOLTP\Data\HKDB_data.mdf’, size=500MB),

FILEGROUP [InMemOLTP_fg] CONTAINS MEMORY_OPTIMIZED_DATA

(NAME = [InMemOLTP_mod_dir],

FILENAME = ‘I:\453491\InMemOLTP\InMemOLTP_mod_dir’)

LOG ON (name = [InMemOLTP_log],

Filename=’I:\453491\InMemOLTP\Log\InMemOLTP_log.ldf’, size=500MB)

COLLATE Latin1_General_100_BIN2;

Here, I have created a filegroup ‘InMemOLTP_fg’ with filecontainer ‘InMemOLTP_mod_dir’. I have also specifies BIN2 collation as currently indexes on character column can only be defined on columns that uses BIN2 collation.

Below is the script to create a memory optimized table with name ‘Test_OLTP’.

CREATE TABLE Test_OLTP

(

       Name varchar(40) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT =10000),

       Place varchar(30),

       CompanyName varchar(40),

       Skill varchar(100)

) WITH (Memory_Optimized = ON, DURABILITY = SCHEMA_AND_DATA);

Syntax to create memory optimized table is similar to the Transact SQL tables but to specify a memory optimized table we need to define clause MEMORY_OPTIMIZED = ON. Memory optimized table can be defined using two durability – SCHEMA_AND_DATA and SCHEMA_ONLY.

With SCHEMA_ONLY durability, memory optimized table will store only table structure and SQL server will not store log changes to the disk. With SCHEMA_AND_DATA, memory optimized table store table data to the checkpoint files on disk and table structure too.

Nonclustered Hash index is created on Name column as clustered indexes are not allowed on memory optimized tables. If you don’t specify Nonclustered with Primary Key column you will get error –

Msg 12317, Level 16, State 78, Line 21

Clustered indexes, which are the default for primary keys, are not supported with hash indexes. Specify a NONCLUSTERED index instead.

When we create a HASH index we need to specify the number of buckets in the hash index. More on Bucket_count I will discuss later in further posts.

Now, the table has been created, let’s try to insert some data into memory optimized table.

INSERT INTO Test_OLTP (Name, Place, CompanyName, Skill)

VALUES (‘Kapil’, ‘Gurgaon’,’CTS’,’SQL Server’),

              (‘Aditya’,’Pune’,’ABC’,’Weblogic’)

Just check the data that you has inserted by running below query.

SELECT * FROM Test_OLTP

OLPT1

So friends, in this way we can create memory optimized database and table. We will discuss further on memory optimized tables in my next blogs.

Have a happy learning 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s