SQL Server 2014 – Hekaton

The most talked about new feature for SQL Server 2014 is the Hekaton in-memory OLTP tables. These structures are optimized for retrieving data from memory rather than from disk.
Kalen Delaney has a great Hekaton White Paper available that explains the features.

Creating An In-Memory Table:

First you’ll create a separate filegroup that uses the ‘memory_optimized_data’ attribute to indicate that it will be making use of the in-memory features. There can be only one of these filegroups in a database.

alter database Rob_Test
add filegroup Hekaton
contains memory_optimized_data

alter database Rob_Test
add file (
name = ‘Hekaton’,
filename = ‘E:\SqlData\SS2014\Hekaton’ — Directory Name
to filegroup [Hekaton]

Second, you’ll create the table, similar to how a regular table would be created, but with some new attributes.

— HekatonTable

if object_id('HekatonTable') is not null
drop table HekatonTable

create table HekatonTable (
RecordId int not null primary key nonclustered hash with (bucket_count = 1024),
RecordType varchar(5) collate Latin1_General_100_BIN2 not null,
DisplayName varchar(20) not null
with (memory_optimized = on, durability = schema_and_data)

The ‘memory_optimized’ property is set to true to indicate this in an in-memory structure, so it will be created in a file in the filegroup we specified in step #1.
‘Durability’ can be set to either ‘schema_only’ for a Non-Durable table or to ‘schema_and_data’ for a Durable table. With a Non-Durable table, the data will be lost when SQL Server is restarted, but no transaction logging occurs for this structure.

I received a few errors when first trying to create a table. When trying to create an index:
— The operation 'CREATE INDEX' is not supported with memory optimized tables.
So you won’t be able to add an index to the table, they have to be added when the table is created. Any indexes have to be non-clustered hash indexes.

Also, since I may look up records by the ‘RecordType’ value, I needed to use a BIN2 collation in order to use a natively compiled stored procedure that looks up on that column (more on these procedures later).

The primary key is set up as a non-ordered hash index. This is a covering index, so all the table’s columns are included. The ‘bucket_count’ value will indicate how many containers to create for the hash index. Delaney’s white paper recommends setting this value where each distinct value in the index will have its own bucket, so for a primary key there would be a bucket for each expected row, although the setting needs to be a power of 2.

The Memory-Optimized tables don’t allow check constraints, foreign keys or triggers.

Natively compiled stored procedures:

To access the Memory-Optimized tables, we can create stored procedures that are compiled to optimize data access.

if object_id('GetHekatonTableByRecordId') is not null
drop procedure GetHekatonTableByRecordId
create procedure GetHekatonTableByRecordId
@RecordId int
with native_compilation, schemabinding, execute as owner
as begin
atomic with
(transaction isolation level = snapshot,
language = 'english')

select RecordId, RecordType, DisplayName
from dbo.HekatonTable
where RecordId = @RecordId



The ‘native_compilation’ attribute is included to indicate that we want the procedure optimized for in-memory access.
Requirements also included using ‘schemabinding’ (To stop the table from being dropped, or for the table definition to be changed so that the proc is broken) and and execution context (Execute as Caller is not supported).
We also define the procedure as one Atomic block, so that all operations in the procedure succeed or fail as a unit. Defining the Atomic block requires a transaction isolation level and a language to be specified.
Make sure to use two part names to refer to the table, otherwise an exception will be raised.
Also, we’re fine with returning varchar/nvarchar values in the select, but if we include a string valued column in the WHERE clause, we’ll have to use a BIN (Binary) collation, as we did for the RecordType column in the table example.


One Response to SQL Server 2014 – Hekaton

  1. […] I posted on In-Memory tables for SQL Server 2014, although several changes were made for 2016. 2016 supports foreign keys, check contraints, outer joins, among other features. […]

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

%d bloggers like this: