Creating In Memory OLTP Tables

This is second article on In Memory table.
Lets see how to create In Memory tables.

Lets create a new database.

InMemory_Test


First we need to add filegroup for memory optimized data

After adding filegroup we will add file to this filegroup

Now the database is ready


Before creating table we have to note few points
There are some more points also but we will lookinto it later


1.Index must be created at time creating table
2.Hash bucket size should be 2 times or more than count distinct values in the coumn
If bucket size is larger it will not be issue but if its smalled it will degrade performance
3.We have to decide its durability
    1.schema_only
    Only table structure will be stored no data will be stored.
    Once server will crash or restart the data will lost
    2.schema_and_data
    Both scema and data will be stored in server.
    After crash or restarting data will be read from disk.


Now lets create tables also we will check its performance


1.Diskbased table

CREATE TABLE t1

  (

     id   INT PRIMARY KEY,

     data VARCHAR(100)

  )

2.Table with schema_only as durability

CREATE TABLE t2

  (

     id   INT  NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),

     data VARCHAR(100)

  )

  WITH(MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY)

If we want durability we have to set DURABILITY to SCHEMA_AND_DATA



Now we will insert data into tables and check performance

SET NOCOUNT ON

DECLARE @date1 DATETIME

DECLARE @date2 DATETIME

DECLARE @i INT

SET @i = 1

SELECT @date1 = Getdate()

WHILE @i <= 100000

  BEGIN

      INSERT INTO t1

      SELECT @I,

             'data' + CONVERT(VARCHAR(10), @i)

      SET @i = @i + 1

  END

SELECT @date2 = Getdate()

SELECT 'Disk Based Table' ,Datediff(ss, @date1, @date2)

go

DECLARE @date1 DATETIME

DECLARE @date2 DATETIME

DECLARE @i INT

SET @i = 1

SELECT @date1 = Getdate()

WHILE @i <= 100000

  BEGIN

      INSERT INTO t2

      SELECT @I,

             'data' + CONVERT(VARCHAR(10), @i)

      SET @i = @i + 1

  END

SELECT @date2 = Getdate()

SELECT 'InMemory Schema Only',Datediff(ss, @date1, @date2)

Here we can see that Diskbased table took 23 seconds while InMemory Table took only  4 seconds.

The difference is clearly visible and tempts us to use it ASAP :-)