CREATE DATABASE HKDB

ON

PRIMARY(NAME = [HKDB_data],

FILENAME = 'E:\MSSQL2014-UserData\HKDB_data.mdf', size=500MB),

FILEGROUP [HKDB_mod_fg] CONTAINS MEMORY_OPTIMIZED_DATA

(NAME = [HKDB_mod_dir_delta],

FILENAME = 'E:\MSSQL2014-UserData\Delta'),

(NAME = [HKDB_mod_dir_ckpt],

FILENAME = 'E:\MSSQL2014-UserLog\ckpt')

LOG ON (name = [HKDB_log],

Filename='E:\MSSQL2014-UserLog\HKDB_log.ldf', size=500MB);


Just a typical, DB Creation Script with a few changes. The key things to note are

1) File Group : The additional file group [HKDB_mod_fg]. In memory tables require a dedicated file group. The "In memory table" file group is similar to file stream file groups.

2) "CONTAINS MEMORY_OPTIMIZED_DATA" keyword indicates that file group would be storing in memory data

3) Checkpoint & Delta files: 2 containers namely "HKDB_mod_dir_delta", "HKDB_mod_dir_ckpt" are created. Folders "Delta", "ckpt" will be automatically created by the command. Just the path needs to be present. The containers are used to store two types of "In Memory" table's files - "Checkpoint" files and "Delta" files.

           "Checkpoint" files or the "Data" files  - Used to store 
            the actual data of the in Memory tables

           "Delta" files - Used to store the flag / pointers to 
           deleted rows in "Data" files.

  We will look at "Checkpoint" and "Delta" files in detail in upcoming posts. Takeaway is "In Memory" tables require a file group with 2 containers to store its data

4) Transaction Log : Note that Transaction log is just one which implies in Memory data will also be using the same Transaction Log.

More again in upcoming posts