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
Phản hồi
https://aka.ms/ContentUserFeedback.
Sắp ra mắt: Trong năm 2024, chúng tôi sẽ dần gỡ bỏ Sự cố với GitHub dưới dạng cơ chế phản hồi cho nội dung và thay thế bằng hệ thống phản hồi mới. Để biết thêm thông tin, hãy xem:Gửi và xem ý kiến phản hồi dành cho