Accessing Memory-Optimized Tables Using Interpreted Transact-SQL
Applies to: 
SQL Server
Azure SQL Database 
Azure Synapse Analytics
Analytics Platform System (PDW)
With only a few exceptions, you can access memory-optimized tables using any Transact-SQL query or DML operation (select, insert, update, or delete), ad hoc batches, and SQL modules such as stored procedures, table-value functions, triggers, and views.
Interpreted Transact-SQL refers to Transact-SQL batches or stored procedures other than a natively compiled stored procedure. Interpreted Transact-SQL access to memory-optimized tables is referred to as interop access.
Starting with SQL Server 2016 (13.x), queries in interpreted Transact-SQL can scan memory-optimized tables in parallel, instead of just in serial mode.
Memory-optimized tables can also be accessed using a natively compiled stored procedure. Natively compiled stored procedures are recommended for performance-critical OLTP operations.
Interpreted Transact-SQL access is recommended for these scenarios:
Ad hoc queries and administrative tasks.
Reporting queries, which typically use constructs not available in natively compiled stored procedures (such as window functions, sometimes referred to as OVER functions).
To migrate performance-critical parts of your application to memory-optimized tables, with minimal (or no) application code changes. You can potentially see performance improvements from migrating tables. If you then migrate stored procedures to natively compiled stored procedures, you may see further performance improvement.
When a Transact-SQL statement is not available for natively compiled stored procedures.
However, the following Transact-SQL constructs are not supported in interpreted Transact-SQL stored procedures that access data in a memory-optimized table.
| Area | Unsupported |
|---|---|
| Access to tables | TRUNCATE TABLE MERGE (memory-optimized table as target) Dynamic and keyset cursors (these automatically degrade to static). Access from CLR modules, using the context connection. Referencing a memory-optimized table from an indexed view. |
| Cross-database | Cross-database queries Cross-database transactions Linked servers |
Table Hints
For more information about table hints, see. Table Hints (Transact-SQL). The SNAPSHOT was added to support In-Memory OLTP.
The following table hints are not supported when accessing a memory-optimized table using interpreted Transact-SQL.
HOLDLOCK
PAGLOCK
READUNCOMMITTED
TABLOCKXX
IGNORE_CONSTRAINTS
READCOMMITTED
ROWLOCK
UPDLOCK
IGNORE_TRIGGERS
READCOMMITTEDLOCK
SPATIAL_WINDOW_MAX_CELLS = integer
XLOCK
NOWAIT
READPAST
TABLOCK
When accessing a memory-optimized table from an explicit or implicit transaction using interpreted Transact-SQL, you must do at least one of the following:
Specify an isolation level table hint such as SNAPSHOT, REPEATABLEREAD, or SERIALIZABLE.
Set the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT to ON.
An isolation level table hint is not required for memory-optimized tables accessed by queries running in auto-commit mode.
See Also
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for