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
