SQL Server 2016 New Features In-Memory OLTP enhancements

1. SQL Server In-Memory OLTP – Parallel and heap scan for memory-optimized tables

Memory-optimized tables and indexes in In-memory optimized tables are supported to parallel scan. This improves the performance of queries. Heap scan is also supported which can be performed in parallel which is scanning of all the rows in a table using the in-memory heap data structure used for storing the data.

2. SQL Server In-Memory OLTP -Transact-SQL/TSQL Improvements for memory-optimized tables

a.UNIQUE constraints and indexes are supported.
b.FOREIGN KEY references between memory-optimized tables are supported.
c.CHECK constraints are supported.
d. NULL values are allowed
e.Trigger’s are supported.

3. SQL Server 2016 In-Memory OLTP Storage format is now changed

In-Memory OLTP – The storage format for memory-optimized tables is changed between SQL Server 2014 and 2016. When we upgrade our SQL Server or attach a Database from 2014 to 2016 orrestore from SQL Server 2014 to 2016, the new storage format is serialized and the database is restarted once during database recovery.

4. SQL Server 2016 In-Memory OLTP – Alter Table is now logged

In-Memory OLTP – ALTER TABLE is now logged which runs in parallel by only writing to Log the metadata changes. This greatly reduces log IO and Improves performance.

5. SQL Server 2016 In-Memory OLTP – Statistics are now updated automatically.

In-Memory OLTP – Statistics for memory-optimized tables are now updated automatically. In SQL Server 2016, sampling method to collect statistics is now supported which can avoid the more expensive old full scan method.

6. SQL Server In-Memory OLTP – Altering Memory-Optimized Tables is now supported

a.ADD and DROP indexes. Change bucket_count of hash indexes.
b.Make schema changes: add/drop/alter columns; add/drop constraint.

7. SQL Server In-Memory OLTP – Memory optimized table can have more than length of the 8060 byte page.

A memory-optimized table can now have several columns whose combined lengths are longer than the length of the 8060 byte page. say 3 or 4 of Varchar(4000) by storing it off-row