Unsupported SQL Server Features for In-Memory OLTP
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.
SQL Server Features Not Supported for In-Memory OLTP
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|
Cross-database queries and transactions
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.)
|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.|
Scenarios Not Supported
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.
- MERGE USING source is supported for memory-optimized tables.
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.
Policy-based management (PBM).
- Prevent and log only modes of PBM aren't supported. Existence of such policies on the server may prevent In-Memory OLTP DDL from executing successfully. On-demand and on-schedule modes are supported.
Database containment (Contained Databases) is not supported with In-Memory OLTP.
- Contained database authentication is supported. However, all In-Memory OLTP objects are marked as breaking containment in the dynamic management view (DMV) dm_db_uncontained_entities.
Recently added supports
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
|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.|