Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate SQL, Power BI, Fabric, and AI community-led event. March 31 - April 2. Use code MSCUST for a $150 discount. Prices go up Feb 11th.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance
This topic discusses SQL Server features that aren't supported for use with memory-optimized objects. Plus, the final section lists features that were unsupported for In-Memory OLTP, but later became supported.
The following SQL Server features aren't supported on a database that has memory-optimized objects (including memory-optimized data filegroup).
Unsupported Feature | Feature Description |
---|---|
Data compression for memory-optimized tables. | You can use the data compression feature to help compress the data inside a database, and to help reduce the size of the database. For more information, see Data Compression. |
Partitioning of memory-optimized tables and HASH indexes, and of nonclustered indexes. | The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. For more information, see Partitioned Tables and Indexes. |
Replication | Replication configurations, other than transactional replication to memory-optimized tables on subscribers, are incompatible with tables or views referencing memory-optimized tables. If there is a memory-optimized filegroup, replication using sync_mode='database snapshot' is not supported. For more information, see Replication to Memory-Optimized Table Subscribers. |
Mirroring | Database mirroring is not supported for databases with a MEMORY_OPTIMIZED_DATA filegroup. For more information about mirroring, see Database Mirroring (SQL Server). |
Rebuild log | Rebuilding the log, either through attach or ALTER DATABASE, is not supported for databases with a MEMORY_OPTIMIZED_DATA filegroup. |
Linked Server | You cannot access linked servers in the same query or transaction as memory-optimized tables. For more information, see Linked Servers (Database Engine). |
Bulk logging | Regardless of the recovery model of the database, all operations on durable memory-optimized tables are always fully logged. |
Minimal logging | Minimal logging is not supported for memory-optimized tables. For more information about minimal logging, see The Transaction Log (SQL Server) and Prerequisites for Minimal Logging in Bulk Import. |
Change tracking | Change tracking is not supported for memory optimized tables. |
DDL triggers | Database-level triggers and server-level DDL triggers aren't supported with In-Memory OLTP tables or with natively compiled modules. |
Change Data Capture (CDC) | SQL Server 2017 CU15 and higher support enabling CDC on a database that has memory optimized tables. This is only applicable to the database and any on-disk tables in the database. In earlier versions of SQL Server, CDC cannot be used with a database that has memory-optimized tables, because internally CDC uses a DDL trigger for DROP TABLE. |
Fiber mode | Fiber mode is not supported with memory-optimized tables: If fiber mode is active, you cannot create databases with memory-optimized filegroups, nor can you add memory-optimized filegroups to existing databases. You can enable fiber mode if there are databases with memory-optimized filegroups. However, enabling fiber mode requires a server restart. In that situation, databases with memory-optimized filegroups would fail to recover. Then you would see an error message suggesting that you disable fiber mode to use databases with memory-optimized filegroups. If fiber mode is active, attaching and restoring a database that has a memory-optimized filegroup fails. The databases would be marked as suspect. For more information, see lightweight pooling Server Configuration Option. |
Service Broker limitation | Cannot access a queue from a natively compiled stored procedure. Cannot access a queue in a remote database in a transaction that accesses memory-optimized tables. |
Replication on subscribers | Transactional replication to memory-optimized tables on subscribers is supported, but with some restrictions. For more information, see Replication to Memory-Optimized Table Subscribers |
With a few exceptions, cross-database transactions aren't supported. The following table describes which cases are supported, and the corresponding restrictions. (See also, Cross-Database Queries.)
Databases | Allowed | Description |
---|---|---|
User databases, model, and msdb. | No | In most cases, cross-database queries and transactions are not supported. A query cannot access other databases if the query uses either a memory-optimized table or a natively compiled stored procedure. This restriction applies to transactions and queries. The exceptions are the system databases tempdb and master. Here the master database is available for read-only access. |
Resource database, tempdb | Yes | In a transaction that touches In-Memory OLTP objects, the Resource and tempdb system databases can be used without added restriction. |
Accessing memory-optimized tables by using the context connection from inside CLR stored procedures.
Keyset and dynamic cursors on queries accessing memory-optimized tables. These cursors are degraded to static and read-only.
Using MERGE INTO target, where target is a memory-optimized table, is unsupported.
The ROWVERSION (TIMESTAMP) data type is not supported. For more information, see FROM (Transact-SQL).
Auto-close is not supported with databases that have a MEMORY_OPTIMIZED_DATA filegroup
Transactional DDL, such as CREATE/ALTER/DROP of In-Memory OLTP objects, is not supported inside user transactions.
Event notification.
Policy-based management (PBM).
Database containment (Contained Databases) is not supported with In-Memory OLTP.
Sometimes a newer release of SQL Server adds support for a feature that was previously not supported. This section lists features that used to be unsupported for In-Memory OLTP, but that later became supported for In-Memory OLTP.
In the following table, version values such as (15.x)
refer to the value that is returned by the Transact-SQL statement SELECT @@Version;
.
Feature name | Version of SQL Server | Comments |
---|---|---|
Database snapshots | 2019 (15.x) | Database snapshots are now supported for databases that have a MEMORY_OPTIMIZED_DATA filegroup. |
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate SQL, Power BI, Fabric, and AI community-led event. March 31 - April 2. Use code MSCUST for a $150 discount. Prices go up Feb 11th.
Register today