Share via

In-Memory OLTP - what's new in SQL2016 CTP3

  • Article

We have been hard at work to add features to In-Memory OLTP to make it easier to build apps and to adopt In-Memory OLTP in existing apps, as well as to increase overall performance and scaling. Below is the list of In-Memory OLTP enhancements that we have released in SQL2016 thus far, up to and including CTP3. There is more to come!

Let us know what you think!

 

Transact-SQL Improvements:

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 with memory-optimized tables for:

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. [Edit 7/1/2016: the there is no longer a limit on user data size with memory-optimized tables see this blog for details]
  • Scalability improvements in the persistence layer
  • Parallel plan support for Accessing Memory-Optimized Tables Using Interpreted Transact-SQL
  • Parallel scan support 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 a SQL Server database.
  • Generate migration checklists by right-clicking on a database, and selecting Tasks -> Generate In-Memory OLTP migration checklists

Cross-feature support:

 

For more information, see In-Memory OLTP (In-Memory Optimization).

Comments

  • Anonymous
    November 12, 2015
    Is it just me or the links can't be accessed?

  • Anonymous
    November 12, 2015
    The issue with the links should now be fixed. Thanks for pointing this out.

  • Anonymous
    November 12, 2015
    This is great. I've never seen Microsoft move so quickly and consistently towards a firm goal. I feel like you're really planning on delivering on the promises you've made. I'm trying to decide if I can use natively compiled code or memory-optimized tables to enhance anything I have, but we have at most a few 100 concurrent users. Is there any use to the natively compiled procedures in an ETL transformation system?

  • Anonymous
    November 12, 2015
    Jeff, for ETL, you can use SCHEMA_ONLY memory optimized table to eliminate any overhead with logging/storage. You can use native SP on this table but the native SP can only contain memory-optimized tables and NOT disk-based tables at this time. One question - assume target of ETL is a Data Warehouse. Are you considering columnstore index?

  • Anonymous
    November 16, 2015
    The comment has been removed

  • Anonymous
    November 16, 2015
    Aaron, that is correct. We currently only support foreign keys referencing primary keys.

  • Anonymous
    November 19, 2015
    It feels annoying sometimes when working with Oracle admins, like when they say - come on, this is sql server and it is not complete database when any silly thing they notice . However I don't want to argue explaining the facts to a dumb guy , can't we figure out and make the product more robust with even silly things fixed......

  1. We can't directly export column headers to csv or excel using bcp utility, ofcourse there are work-arounds . However good to have inbuilt than to write some custom scripts 2)Whenever we add any object in database (table, Store Procedure or Logins , users etc), unless you refresh the parent , it isn't visible. Can it be fixable. May not be great things to point out and seek expert advice......however I always feel that introducing few features will not limit the product to strengthen its roots. Regards, Srikanth vsramja@gmail.com