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.
- 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
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
- 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!Tags: in-memory oltp, sql server 2016 Last modified: September 23, 2021