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

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:
LEFT and RIGHT OUTER JOIN
SELECT DISTINCT
OR and NOT operators
Subqueries in all clauses of a SELECT statement
Nested stored procedure calls
UNION and UNION ALL
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)
varbinary(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“.

Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Artemakis's official website can be found at aartemiou.com.