Part of a Series on "Hekaton". Refer below for previous posts
Having created a "Hekaton" database, Let us create a "In Memory" table
CREATE TABLE dbo.HK_tbl
(
[ID] Int Not null PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
[Data] char(32) COLLATE Latin1_General_100_BIN2 null,
[dt] datetime not null,
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
Looks like a typical table creation script. Points to note are
1) Definition of primary key - As No ALTER is allowed in SQL 2014, constraint needs to be defined inline. ALTER statement is supported in SQL 2016 and restriction applies only on SQL Server 2014.
2) Keywords "NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000)" - specifies hash index to be used. More on that in upcoming posts.
3) "MEMORY_OPTIMIZED = ON" clause - indicates that the table will be a in memory table
4) "DURABILITY = SCHEMA_AND_DATA" - Indicates that in Memory table will be written to the disk like traditional disk based table. "DURABILTY= SCHEMA_ONLY" would indicate that table would be only in Memory and data would be lost after restart.
5) Collation : - Column "[Data]" has been defined under collation "Latin1_General_100_BIN2". Char and Varchar columns are to be defined only on "Latin1_General_100_BIN2". However, the restriction is removed in SQL Server 2016.
Next post will perform a quick performance comparison of disk based and "In Memory" table.
