SQL Server In-Memory OLTP Part 1 - Article on SQLNetHub

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

by

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

Learn More

 

Featured Online Courses:

 

Check our other related SQL Server Performance articles.

Subscribe to our newsletter and stay up to date!

Check out our latest software releases!

Check out our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 2,574