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)
  • 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:

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:

There is more to come!

Andrey Langovoy

Andrey Langovoy

Andrey Langovoy is a team leader at Devart. He takes part in development and testing database management tools for SQL Server, writes articles about SQL Server and contributes to open source projects, MSDN and MDN.