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

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
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 / LimitationSQL Server 2014SQL Server 2016 CTP2
Maximum memory for memory-optimized tablesRecommendation (not hard limit): 256 GBRecommendation (not hard limit): 2TB
Collation supportMust 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 SupportedSupported
Alter natively-compiled stored proceduresNot SupportedSupported
Parallel plan for operations accessing memory-optimized tablesNot SupportedSupported
Transparent Data Encryption (TDE)Not SupportedSupported
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 SupportedSupported
DML triggers in memory-optimized tablesNot SupportedSupported
(AFTER triggers, natively-compiled)
Multiple Active Result Sets (MARS)Not SupportedSupported
Large Objects (LOBs):
varchar(max)
nvarchar(max)
varbinary(max)
Not SupportedSupported
Offline Checkpoint Threads1Multiple Threads
Natively-compiled, scalar user-defined functionsNot SupportedSupported
Indexes on NULLable columnsNot SupportedSupported

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“.


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics 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). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.