Written by 07:49 Database administration, Memory Optimization

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!

Tags: , Last modified: September 23, 2021