Storage Format ModificationThe storage format of the memory-optimized table has been modified in 2014,2016, the new format is sequence and the database is restarted once during database recovery.
ALTER table log is optimized and can be run concurrentlyWhen you execute ALTER TABLE in a memory-optimized table, only the changes to the metadata are written to the log. This reduces the number of logs. And a lot of alter TABLE is parallel, which can reduce the time of statement run
- However, if you include lobs, you cannot run in parallel
Statistical InformationThe statistics for the optimized table can be updated automatically. You can also collect statistics by sampling and do not require a table scan
concurrency and heap scanning for memory-optimized tablesMemory-Optimized tables and indexes, just parallel scans. For analytic queries, you can improve performance. In addition, the heap scan also carries out concurrent scans.
TSQL Enhancements for memory-optimized tablesMany TSQL statements are not available in SQL Server 2014 and are now available on 2016:
- Supports UNIQUE constraints and indexes
- Support for foreign keys between memory-optimized tables
-
- A foreign key can only refer to a primary key and cannot be created with a unique
- Support for CHECK constraints
- Non-unique indexes support NULL in key
- Support for triggers on memory-optimized tables
-
- Only after triggers are supported, INSTADOF triggers do not support
- Any triggers in a memory-optimized table must use the with Native_compilation.
- Memory-Optimized tables, multiple columns, and row sizes can exceed 8060 bytes.
- LOB Type Support
natively compiled's tsql enhancementSlightly
improved performance and scalability
- Data size is not limited
- Supports multiple threads to persist modification of memory-optimized tables to disk
- Interop supports multithreaded access
SSMs EnhancementsSlightly
SQL Server 2016 new features: In-memory OLTP
