Tuesday, October 13, 2015

In-Memory OLTP: Comparison of Features/Limitations between SQL Server 2014 and SQL Server 2016

In earlier articles I talked about the In-Memory OLTP Engine in SQL Server 2014. Even though it is very powerful, it had some limitations (note the past tense of "have" here as I have some good news! :)

For example you couldn't use subqueries in the clauses of a SELECT statement inside a natively-compiled stored procedure, or nested stored procedure calls, etc.

Here's the good news: SQL Server 2016 (currently CTP 2.4) lifted all these limitations and provides far more support for In-Memory OLTP, thus making it much easier to use this cool technology.

The table below summarizes the features/limitations of the in In-Memory OLTP Engine in SQL Server 2014 against SQL Server 2016:

Feature / Limitation SQL Server 2014 SQL Server 2016 CTP2
Maximum memory for memory-optimized tables Recommendation (not hard limit): 256 GB Recommendation (not hard limit): 2TB
Collation support Must use a *_BIN2 collation for:
(i) Character columns used as all or part of an index key.
(ii) All comparisons/sorting between character values in natively-compiled modules.

Must use Latin code pages for char and varchar columns.
All collations are fully supported
Alter memory-optimized tables (after creation) Not Supported Supported
Alter natively-compiled stored procedures Not Supported Supported
Parallel plan for operations accessing memory-optimized tables Not Supported Supported
Transparent Data Encryption (TDE) Not Supported Supported
Use of the below language constructs in natively-compiled stored procedures:
- OR and NOT operators
- Subqueries in all clauses of a SELECT statement
- Nested stored procedure calls
- All built-in math functions
Not Supported Supported
DML triggers in memory-optimized tables Not Supported Supported
(AFTER triggers, natively-compiled)
Multiple Active Result Sets (MARS) Not Supported Supported
Large Objects (LOBs):
- varchar(max)
- nvarchar(max)
Not Supported Supported
Offline Checkpoint Threads 1 Multiple Threads
Natively-compiled, scalar user-defined functions Not Supported Supported
Indexes on NULLable columns Not Supported Supported

As a last note, if you want to easily test the In-Memory OLTP Engine of SQL Server, you can download the special software I developed for this purpose called "In-Memory OLTP Simulator".

Check out my latest eBooks on SQL Server:
Administering SQL Server - Ebook Tuning SQL Server - Ebook

Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)