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