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
|DML triggers in memory-optimized tables||Not Supported||Supported
(AFTER triggers, natively-compiled)
|Multiple Active Result Sets (MARS)||Not Supported||Supported|
|Large Objects (LOBs):
|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“.