Using SQL Server Enterprise 2016, Is compatibility level 130 "REQUIRED" for Memory Optimized Tables?

Question

Using SQL Server Enterprise 2016, Is compatibility level 130 "REQUIRED" for Memory Optimized Tables?

Dean Marchetti 5

We are running SQL Server Enterprise 2016 compatibility level 100 for customer reasons and cannot upgrade at this time or any time soon. We want to employ "Memory Optimized Tables" with this customer. The documentation "suggests" using compatibility level 130 as a minimum. However, we have found no documentation stating this is a minimum requirement. We have done extensive testing with Compatibility 100 and have found no issues. So, the question:

For SQL Server Enterprise 2016, Is compatibility level 130 "REQUIRED" for Memory Optimized Tables?

LiHongMSFT-4306 31,571 Reputation points

2023-11-29T07:23:30.6866667+00:00

The documentation "suggests" using compatibility level 130 as a minimum. However, we have found no documentation stating this is a minimum requirement.

Not found compatibility level limitation from this doc: Introduction to Memory-Optimized Tables.

Could you post the documentation that says using compatibility level 130 as a minimum?

1 answer

Your answer

LiHongMSFT-4306 31,571 Reputation points

2023-11-29T07:23:30.6866667+00:00

The documentation "suggests" using compatibility level 130 as a minimum. However, we have found no documentation stating this is a minimum requirement.

Not found compatibility level limitation from this doc: Introduction to Memory-Optimized Tables.

Could you post the documentation that says using compatibility level 130 as a minimum?

Answer 1

Erland Sommarskog 121.9K MVP Volunteer Moderator

Do you have a link to this particular piece of documentation?

I'm tempted to say that if it works, it works. But I still like to see what the documentation actually says.

Ian Dembsky 0 Reputation points

2023-11-29T14:29:13.1566667+00:00

Here is a link from the Microsoft Documentation clearly recommending 130 as a minimum:

https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/survey-of-initial-areas-in-in-memory-oltp?view=sql-server-ver16#1-ensure-compatibility-level--130
Dean Marchetti 5 Reputation points

2023-11-29T14:37:59.5766667+00:00

https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/survey-of-initial-areas-in-in-memory-oltp?view=sql-server-ver16

This is a whitepaper from MSFT that discusses In Memory.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2023-11-29T22:03:43.01+00:00
I'm not sure I would call that a whitepaper. It's a "survey" with some quick "get-started" points. Possibly some of the steps use syntax that require compat level 130.

Generally compat level controls:

Availability breaking syntax changes. For instance, the string_split function requires compat level 130, since you could have your own UDF called string_split.

Behavioural changes. For instance there are some difference with rounding and conversion with compat level 130.

New optimizer features are typically only available with the most recent compat level.

I can't see that In-Memory OLTP fits in here, although there could be nuances that are different betweee SQL 2016 and SQL 2014 (which was the first version where In-Memory OLTP appeared.)

And I would certainly not interpret that document as REQUIRING compat level 130.

Share via

Using SQL Server Enterprise 2016, Is compatibility level 130 "REQUIRED" for Memory Optimized Tables?

1 answer

Your answer