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
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.
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
with native_compilation, schemabinding, execute as owner
(transaction isolation level = snapshot,
language = 'english')
select RecordId, RecordType, DisplayName
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.