One in a series of posts about Microsoft’s In-Memory OLTP Functionality

So, back in SQL Server 2014, Microsoft introduced a new technology by the name of In-Memory OLTP.  It was code named Hekaton, which is a much cooler name.  And shorter.  And easier to say.  So I’m just going to keep calling it Hekaton.

This post is part one of what I expect will be a long series of brain dumps on how to use Hekaton as well as some of the internal that I have picked up over the past couple of years.  With the run-up to SQL Server 2016, we’ll also see some pretty good enhancements, so I’ll address these as well in due course.

Back when Hekaton was first announced (and once I started to wrap my head around it), I thought it was the coolest thing ever, and that it was going to revolutionize SQL Server.  Once it came out, it was quickly appparent that it was a “version 1” product and that there were just too many limitations around it to be truly useful from the get-go.

So it didn’t exactly set the world on fire, and even with many improvements coming in “version 2,” it still has a ways to go.  I am still firmly of the belief that the day will come when in-memory technology will become the de facto standard for new tables.

So, with that background and that hope for the future, let’s start with how to enable Hekaton at the database and how to create a new in-memory table.

create database Hekaton
on primary (name = 'Hekaton',
	filename = 'D:\sql\data\Hekaton.mdf',
	size = 25600KB, filegrowth = 5120KB),
filegroup InMemoryFileGroup contains memory_optimized_data
	(name = 'InMemoryFiles',
	filename = 'D:\sql\HekatonInMemoryFileGroup')
log on (name = 'Hekaton_log',
	filename = 'D:\sql\log\Hekaton_log.ldf',
	size = 10240KB, filegrowth = 10240KB);

use Hekaton;

create table Employee
(
	EmployeeID int not null,
	FirstName varchar(50) not null,
	LastName varchar(50) not null,
	Salary money not null,
	constraint PK_Employee primary key nonclustered
		(EmployeeID)
) with (memory_optimized = on, durability = schema_and_data);