Working with Memory-Optimized System-Versioned Temporal Tables
Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance
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 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
משוב
https://aka.ms/ContentUserFeedback.
בקרוב: במהלך 2024, נפתור בעיות GitHub כמנגנון המשוב לתוכן ונחליף אותו במערכת משוב חדשה. לקבלת מידע נוסף, ראה:שלח והצג משוב עבור