In SQL Server 2014 a new feature In-Memory OLTP was introduced which was referred as ‘Hekaton’. This feature was introduced to work with memory-optimized tables rather than working with disk based tables to improve the performance.

But in SQL Server 2016 CTP2 release some enhancement are made to In-Memory OLTP features which are described below:

Features SQL Server 2014 SQL Server 2016
Maximum
memory for memory

optimized tables
256 GB 2 TB
Collation Characters
columns must use bin2

Collation which are part of index key.
This
restriction is removed and

Character columns using any

Collation can be part of
index.
Schema
and data changes
Not
allowed any changes after

Table creation.
Alter
table statement can be

used to add, drop or alter columns.
Parallel
plans
Not
supported in this version
Operations
that uses hash indexes

can be performed in parallel.
Transparent
data encryption
Not
supported in this version
Supported
in sql 2016 and

Memoryoptimized tables data

can be encrypted
LOB
datatypes
Not
supported
Supported
Left
and Right outer join
Not supported Supported
Select
Distinct
Not Supported Supported
Subqueries
in clause of

select statement
Not supported Supported
Nested
stored procedure calls
Not supported Supported
UNION
and UNION ALL
Not supported Supported
Foreign
keys
Not supported Supported
Multiple
log reader threads
Used
only one log reader thread

per database
Allow
multiple threads for both

recovery and checkpoint
no
of sockets
limited
scalability with

multiple socket machines
efficient
scalability with a

4-socket machine.
AlwaysOn Data
visibility of in-memory oltp on

Secondary replica was delayed by

few transaction
this
limitation is removed in sql server

2016 and data from both disk-based and

memory-based visible to user at same time.
DML
Triggers
Not
supported
Partially
supported (after,

natively compiled)
Index
on null column
Not
supported
Supported

 

That’s all for the day folks.

 

Regards,

Kapil Singh Kumawat

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook