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

 

Major Enhancements for In-Memory OLTP in SQL Server 2016

Here’s the good news: SQL Server 2016 lifted all these limitations and provides far more support for In-Memory OLTP, thus making it much easier to use this outstanding technology.

 

Table with In-Memory OLTP Enhancements in SQL Server 2016

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 
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 note, if you want to easily test the In-Memory OLTP Engine of SQL Server, you can download the 14-day trial of a special software I developed for this purpose called “In-Memory OLTP Simulator“.

 

Enroll to the Online Course!

Enroll to our online course titled  “Boost SQL Server Database Performance with In-Memory OLTP” and learn how to Harness the Power of SQL Server’s In-Memory Optimization and Boost the Performance of your Data Processes!

Boost SQL Server Database Performance with In-Memory OLTP - Online Course
(Lifetime Access, Certificate of Completion, Live Demos and more).

Enroll Now with Discount!

 

Featured Online Courses: