Work with memory-optimized system-versioned temporal tables
Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance
This article discusses how working with a memory-optimized system-versioned temporal table is different from working with a disk-based system-versioned temporal table.
Note
Memory-optimized temporal tables are only available in SQL Server, and not Azure SQL Database.
Discover metadata
To discover metadata about a memory-optimized system-versioned temporal table, you need to combine information from sys.tables and sys.internal_tables. 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
INNER JOIN sys.tables T1
ON IT.parent_object_id = T1.object_id
INNER JOIN sys.tables T2
ON T1.history_table_id = T2.object_id
WHERE T1.is_memory_optimized = 1
AND T1.temporal_type = 2;
Modify data
Memory-optimized temporal tables can be modified through natively compiled stored procedures, which enable you to convert non-temporal memory-optimized tables, 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;
Related content
- System-versioned temporal tables with memory-optimized tables
- Create a memory-optimized system-versioned temporal table
- Monitor memory-optimized system-versioned temporal tables
- Memory-optimized system-versioned temporal table performance
- Temporal tables
- Temporal table system consistency checks
- Manage retention of historical data in system-versioned temporal tables
- Temporal table metadata views and functions