SQL Server 2016 introduced several enhancements in the InMemory space, and in this blog post, we will explore some of these capabilities. One question that often comes up is whether SQL Azure supports InMemory capabilities. In the past, this feature was not available, but with the introduction of the V12 version, Azure now supports InMemory.

To check if a database in Azure SQLDB supports InMemory objects such as tables and procedures, you can use the following query:

SELECT SERVERPROPERTY('IsXTPSupported');

If the query returns 1, it confirms that the database supports InMemory objects. Another method to check is by using the DATABASEPROPERTYEX function:

SELECT DATABASEPROPERTYEX ('YourDatabaseName', 'IsXTPSupported');

This function will also return 1 if the database supports InMemory OLTP capability. This method can be used for both Azure DBs and on-premise installations.

However, there are some restrictions when using InMemory OLTP with Azure:

• It can only be used in premium DBs.
• It can only be used in new DBs. You cannot use it in copies of pre-existing databases unless the pre-existing database was InMemory OLTP enabled from private preview.
• Databases with InMemory OLTP objects cannot be downgraded to basic or standard editions. However, if you remove all InMemory OLTP objects, then you can downgrade.
• Performance tier downgrade within premium is blocked for databases with InMemory OLTP objects.
• A database with InMemory OLTP objects cannot be downgraded to a Premium performance tier with a smaller memory size.

It’s important to note that Azure SQL database restrictions are subject to change over time. Some of these restrictions may be lifted in the future, so always refer to the documentation for the latest information.

Are you an Azure SQL database user? We would love to hear about your experiences and what you have been using it for. Let us know in the comments section!