In-Memory OLTP: What’s new in SQL Server 2016

SQL Server development team has added new features to In-Memory OLTP. All these features allow you to build new applications as well as to increase overall performance and scaling. Below is the list of In-Memory OLTP improvements that are available in SQL Server 2016 CTP3.

Transact-SQL Improvements:

Query Surface Area in Native Modules for:

• Disjunction (OR, NOT)
• UNION and UNION ALL
• SELECT DISTINCT
• OUTER JOIN
• Subqueries in SELECT statements (EXISTS, IN, scalar subqueries)
• Nested execution (EXECUTE) of natively compiled modules
• Natively compiled inline table-valued functions (TVFs)
• EXECUTE AS CALLER support – the EXECUTE AS clause is no longer required for native modules
• Built-in security functions and increased support for built-in math functions

Support for memory-optimized tables:

• FOREIGN KEY constraints between memory-optimized tables
• CHECK constraints
• UNIQUE constraints
• Triggers (AFTER) for INSERT/UPDATE/DELETE operations. Triggers on memory-optimized tables are natively compiled and thus use WITH NATIVE_COMPILATION.

ALTER and sp_recompile support for natively compiled procedures, functions, and triggers

ALTER TABLE support for schema and index changes
The new ALTER TABLE … ADD/DROP/ALTER INDEX syntax is introduced for index changes on memory-optimized tables.

Full support for collations and code pages:

• (var)char columns can use any code page supported by SQL Server
• Character columns in index keys can use any SQL Server collation
• Expressions in natively compiled modules, as well as constraints on memory-optimized tables, can use any SQL Server collation

Scalar User-Defined Functions for In-Memory OLTP

Performance and Scaling improvements:

• Increased data size: a database can now have up to 2 terabytes of user data in memory-optimized tables with SCHEMA_AND_DATA
• Scalability improvements in the persistence layer
• Parallel plan support for Accessing Memory-Optimized Tables Using Interpreted Transact-SQL
• Parallel scan supports for HASH indexes

Enhancements in SQL Server Management Studio:

• The transaction analysis report no longer requires the configuration of data collectors or management data warehouse. The report can now run directly on a production database. Simply right-click on the database and select Reports -> Standard Reports -> Transaction Performance Analysis Report.
• PowerShell Cmdlet for Migration Evaluation for evaluating the migration fitness of multiple objects in an SQL Server database.
• Generate migration checklists by right-clicking on a database, and selecting Tasks -> Generate In-Memory OLTP migration checklists

Cross-feature support:

•  Multiple Active Result Sets (MARS) connections can now access memory-optimized tables and natively compiled stored procedures
• Transparent Data Encryption support. If a database is configured for ENCRYPTION, files in the MEMORY_OPTIMIZED_DATA filegroup are now also encrypted.
• Support for using temporal system-versioning with In-Memory OLTP. For more information, see System-Versioned Temporal Tables with Memory-Optimized Tables
• Query store support for natively compiled code from In-Memory OLTP workloads. For more information, see Using the Query Store with In-Memory OLTP.
• Row-Level Security in Memory-Optimized Tables

There is more to come!