Hekaton - Creating a Database :- 2
With a Gentle intro done on Hekaton - In Memory tables, let us continue exploring further. Today, we will quickly create a database which would store "In memory" tables. While we create, we would just explore the various facets on "In memory table" storage design. As the architecture of Hekaton tables is complex, throughout this series we would be taking slow steps to explore them.
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