Skip to content

Latest commit

 

History

History
95 lines (62 loc) · 4.89 KB

accessing-memory-optimized-tables-using-interpreted-transact-sql.md

File metadata and controls

95 lines (62 loc) · 4.89 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic monikerRange
Memory-optimized tables using interpreted T-SQL
Learn about accessing memory-optimized tables using interpreted Transact-SQL (Transact-SQL batches or stored procedures in SQL Server).
MikeRayMSFT
mikeray
05/31/2016
sql
in-memory-oltp
conceptual
=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

Accessing Memory-Optimized Tables Using Interpreted Transact-SQL

[!INCLUDEtsql-appliesto-ss2014-asdb-xxxx-xxx_md]

With only a few exceptions, you can access memory-optimized tables using any [!INCLUDEtsql] 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 [!INCLUDEtsql] refers to [!INCLUDEtsql] batches or stored procedures other than a natively compiled stored procedure. Interpreted [!INCLUDEtsql] access to memory-optimized tables is referred to as interop access.

Starting with [!INCLUDEsssql16-md], queries in interpreted [!INCLUDEtsql] 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 [!INCLUDEtsql] 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 [!INCLUDEtsql] statement is not available for natively compiled stored procedures.

However, the following [!INCLUDEtsql] constructs are not supported in interpreted [!INCLUDEtsql] 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 [!INCLUDEinmemory].

The following table hints are not supported when accessing a memory-optimized table using interpreted [!INCLUDEtsql].

:::row::: :::column::: HOLDLOCK

    PAGLOCK

    READUNCOMMITTED

    TABLOCKXX
:::column-end:::
:::column:::
    IGNORE_CONSTRAINTS

    READCOMMITTED

    ROWLOCK

    UPDLOCK
:::column-end:::
:::column:::
    IGNORE_TRIGGERS

    READCOMMITTEDLOCK

    SPATIAL_WINDOW_MAX_CELLS = *integer*

    XLOCK
:::column-end:::
:::column:::
    NOWAIT

    READPAST

    TABLOCK
:::column-end:::

:::row-end:::

When accessing a memory-optimized table from an explicit or implicit transaction using interpreted [!INCLUDEtsql], you must do at least one of the following:

An isolation level table hint is not required for memory-optimized tables accessed by queries running in auto-commit mode.

See Also

Transact-SQL Support for In-Memory OLTP

Migrating to In-Memory OLTP