Memory-optimized temporal tables

2016 (13.x) and later

System-versioned temporal tables for

memory-optimized tables

provide a 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.

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 long period of time (multiple months, even years), even though it isn’t

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. At the same time, using RAM to store read-only historical data that is

accessed less frequently as it becomes older, isn’t an optimal solution.

System-versioned temporal tables for memory-optimized tables provide high transactional

throughput, and lock-free concurrency. They give you the 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 effect on DML operations is reduced by using an internal,

autogenerated memory-optimized staging table that stores recent history, and enables DMLs

to be executed from natively compiled code.

The following diagram illustrates this architecture.

７

Note

Memory-optimized temporal tables are only available in SQL Server and Azure SQL

Managed Instance. Memory-optimized tables and temporal tables are independently

available in Azure SQL Database.