Working with Memory-Optimized System-Versioned Temporal Tables

APPLIES TO: yesSQL Server (starting with 2016) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

This topic discusses how working with a memory-optimized system-versioned temporal table is different from working with a disk-based system-versioned temporal table.

Note

Using Temporal with memory optimized tables only applies to SQL Server 2017 and does not apply to SQL Database.

Discovering Metadata

To discover metadata about a memory-optimized system-versioned temporal table, you need to combine information from sys.tables (Transact-SQL) and sys.internal_tables (Transact-SQL). A system-versioned temporal table is presented as parent_object_id of the internal in-memory history table

This example shows how to query and join these tables.

SELECT SCHEMA_NAME (T1.schema_id) as TemporalTableSchema  
   , OBJECT_NAME(IT.parent_object_id) as TemporalTableName  
   , T1.object_id as TemporalTableObjectId  
   , IT.Name as InternalHistoryStagingName   
   , SCHEMA_NAME (T2.schema_id) as HistoryTableSchema  
   , OBJECT_NAME (T1.history_table_id) as HistoryTableName   
FROM sys.internal_tables IT    
JOIN sys.tables T1   
   ON IT.parent_object_id = T1.object_id   
JOIN sys.tables T2   
   ON T1.history_table_id = T2.object_id   
WHERE T1.is_memory_optimized  = 1 AND T1.temporal_type = 2  

Modifying Data

System-versioned memory-optimized temporal tables can be modified through natively compiled stored procedures, which enables you to convert non-temporal memory-optimized tables to system-versioning and keep existing natively stored procedures.

This example how previously created table can be modified in natively compiled module.

CREATE PROCEDURE dbo.UpdateFXCurrencyPair  
   (   
       @ProviderID int  
     , @CurrencyID1 int  
     , @CurrencyID2 int  
     , @BidRate decimal(8,4)  
     , @AskRate decimal(8,4)   
   )   
WITH NATIVE_COMPILATION, SCHEMABINDING  
   , EXECUTE AS OWNER   
AS    
   BEGIN ATOMIC WITH   
   (TRANSACTION ISOLATION LEVEL = SNAPSHOT  , LANGUAGE = N'English')   
      UPDATE dbo.FXCurrencyPairs SET AskRate = @AskRate, BidRate  = @BidRate   
     WHERE ProviderID = @ProviderID AND CurrencyID1 = @CurrencyID1 AND CurrencyID2 = @CurrencyID2   
END   
GO ;  

See Also

System-Versioned Temporal Tables with Memory-Optimized Tables
Creating a Memory-Optimized System-Versioned Temporal Table
Monitoring Memory-Optimized System-Versioned Temporal Tables
Performance Considerations with Memory-Optimized System-Versioned Temporal Tables
Temporal Tables
Temporal Table System Consistency Checks
Manage Retention of Historical Data in System-Versioned Temporal Tables
Temporal Table Metadata Views and Functions