System-Versioned Temporal Tables with Memory-Optimized Tables

APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

System-versioned temporal tables for Memory-Optimized Tables are designed to provide cost-effective solution for scenarios where data audit and point in time analysis are required on top of data collected with In-Memory OLTP workloads. They provide high transactional throughput, lock-free concurrency and at the same time, ability to store large amount of history data that can be easily queried.


System-versioned temporal tables automatically keep a full history of data changes and expose convenient Transact-SQL extensions for point in time analysis. In a typical scenario, data history is retained for a very long period of time (multiple months, even years), even though it is not regularly queried.

Data audit and time-based analysis can be demanded in different environments, especially in OLTP systems that process extremely large numbers of requests and where In-Memory OLTP technology is used. However, using memory-optimized tables in temporal scenarios is challenging because a huge amount of generated historical data commonly exceeds the limit of available RAM memory. At the same time using RAM to store read-only historical data that is accessed less frequently as it becomes older is not an optimal solution.

System-versioned temporal tables for memory-optimzed tables provide high transactional throughput, lock-free concurrency and at the same time, ability to store large amount of history data by using in-memory tables for storing current data (the temporal table) and disk-based tables for historical data. The impact on DML operations is minimalized through the use of an internal, auto-generated memory-optimized staging table that stores recent history and enables DMLs to be executed from natively compiled code.

The following diagram illustrates this architecture.Temporal In-Memory Architecture

Implementation Details

The following facts about system-versioned temporal tables with memory optimized tables are considerations of which you need to be aware when creating a system-versioned memory-optimized table. For syntax options and for an example, see CREATE TABLE (Transact-SQL).

  • Only durable memory-optimized tables can be system-versioned (DURABILITY = SCHEMA_AND_DATA).

  • History table for memory-optimized system-versioned table must be disk-based, regardless if it was created by the end user or the system.

  • Queries that affect only the current table (in-memory) can be used in natively compiled T-SQL modules. Temporal queries using the FOR SYSTEM TIME clause are not supported in natively compiled modules. Use of the FOR SYSTEM TIME clause with memory-optimized tables in ad hoc queries and non-native modules is supported.

  • When SYSTEM_VERSIONING = ON, an internal memory-optimized staging table is automatically created to accept the most recent system-versioned changes that are results of update and delete operations on memory-optimized current table.

  • Data from the internal memory-optimized staging table is regularly moved to the disk-based history table by the asynchronous data flush task. This data flush mechanism has a goal to keep the internal memory buffers at less than 10% of the memory consumption of their parent objects. You can track the total memory consumption of memory-optimized system-versioned temporal table by querying sys.dm_db_xtp_memory_consumers (Transact-SQL) and summarizing the data for the internal memory-optimized staging table and the current temporal table.

  • You can enforce a data flush by invoking sp_xtp_flush_temporal_history.

  • When SYSTEM_VERSIONING = OFF or when schema of system-versioned table is modified by adding, dropping or altering columns, the entire contents of the internal staging buffer is moved into the disk-based history table.

  • Querying of historical data is effectively under SNAPSHOT isolation level and always returns a union between in-memory staging buffer and disk based table without duplicates.

  • ALTER TABLE operations that change the table schema internally must perform a data flush, which may prolong the duration of the operation.

The Internal Memory-Optimized Staging Table

The internal memory-optimized staging table is an internal object that is created by the system to optimize DML operations.

  • The table name is generated in the following format: Memory_Optimized_History_Table_<object_id> where <object_id> is identifier of the current temporal table.

  • The table replicates the schema of current temporal table plus one BIGINT column. This additional column guarantees the uniqueness of the rows moved to internal history buffer.

  • The additional column has the following name format: Change_ID[_< suffix>], where _<suffix> is optionally added in the case where the table already has a Change_ID column.

  • The maximum row size for a system-versioned memory-optimized table is reduced by 8 bytes because of the additional BIGINT column in staging table. The new maximum is now 8052 bytes.

  • The internal memory-optimized staging table is not represented in Object Explorer of SQL Server Management Studio.

  • Metadata about this table as well as its connection with current temporal table can be found in sys.internal_tables (Transact-SQL).

The Data Flush Task

The data flush is a regularly activated task that checks whether any memory-optimized table meets a memory size-based condition for data movement. Data movement starts when memory consumption of internal staging table reaches 8% of memory consumption of current temporal table.

The data flush task is activated regularly with a schedule that varies based on the existing workload. With a heavy workload, as frequent as every 5 seconds, and with a light workload, as infrequent as every 1 minute. One thread is spawned for each internal memory-optimized staging table that needs cleanup.

Data flush deletes all records from in-memory internal buffer that are older than the oldest currently running transaction to move these records to the disk-based history table.

You can enforce a data flush by invoking sp_xtp_flush_temporal_history and specifying the schema and table name:
**sys.sp_xtp_flush_temporal_history @schema_name, @object_name**. With this user-executed command, the same data movement process is invoked as when data flush task is invoked by the system on internal schedule.

See Also

Temporal Tables
Getting Started with System-Versioned Temporal Tables
Temporal Table Usage Scenarios
Temporal Table System Consistency Checks
Partitioning with Temporal Tables
Temporal Table Considerations and Limitations
Temporal Table Security
Manage Retention of Historical Data in System-Versioned Temporal Tables
Temporal Table Metadata Views and Functions