Does Azure SQL Database support In Memory Optimized Tables?

According to this MSDN article it is supported in Azure Sql Database and not supported in Azure Sql Data Warehouse. As for Azure Sql Database, it is in preview for Premium Azure SQL databases only. It is not supported neither in Basic nor in Standard tier (see more details in azure-specific documentation).

If you try to develop for Azure Sql Database using Visual Studio, both versions 2013 and 2015 will show you the following error message (your database project won't compile):

SQL71578: The element Table: [dbo].[*******] has property IsMemoryOptimized set to a value that is not supported in Microsoft Azure SQL Database v12.   

At this point you have to consider SQL Server 2014 or 2016 in an Azure VM, but not Azure SQL.

As of Q4 2020, Azure SQL supports In-Memory OLTP (aka MEMORY_OPTIMIZED) in only a couple of pricing-tiers. This is half-documented in this (hard to find) Azure SQL documentation page, the other half I sourced from ancient blog posts and trial-and-error within the Azure Portal.

(Note again, this only applies to "Azure SQL" and not running the full-fat SQL Server in a VM or the Managed Instance service).

Here's a flowchart I made:

• Are you using DTUs or vCores?
  • DTUs:
    • Are you using the Basic tier (5 DTUs)? If so, then no, In-Memory is not supported.
    • Are you using the Standard tier (10-3000 DTUs)? If so, then no, In-Memory is not supported.
    • Are you using the Premium tier (125-4000 DTUs)? If so, then yes, In-Memory is supported.
  • vCores:
    • Are you using the General Purpose tier? If so, then no, In-Memory is not supported.
    • Are you using the Hyperscale tier? If so, then no, In-Memory is not supported.
    • Are you using the Business Critical tier? If so, then yes, In-Memory is supported.

You can find out if MEMORY_OPTIMIZED features are enabled in your database by opening SSMS, connecting to your database (your actual database, not master) and running this:

SELECT DatabasePropertyEx( DB_NAME(), 'IsXTPSupported' ) AS IsXTPSupported;

If you get 0 then In-Memory OLTP is not supported in your database. If you get 1 then it is supported.

And if you try to do CREATE TYPE dbo.Foo AS TABLE ( ... ) WITH ( MEMORY_OPTIMIZED = ON ); you'll get this error:

Msg 40536, Level 16, State 2, Line 2
'MEMORY_OPTIMIZED tables' is not supported in this service tier of the database. See Books Online for more details on feature support in different service tiers of Windows Azure SQL Database.

---

Wrly, I'm disappointed that for all the hype Microsoft has been building for In-Memory OLTP, it isn't available for the majority of Azure SQL customers. Even more surprising is that In-Memory is meant to reduce the IO burden, which should mean that In-Memory OLTP will reduce Microsoft's own operating-costs for Azure. I guess they're waiting to see how customers use it. Given the extra technical details and thorough-understanding of the system required to use it, it's possible that customers will end-up mis-using it - gotta protect users from themselves, y'know.