Creating a Memory-Optimized System-Versioned Temporal Table
Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance
Similar to creating a disk-based history table, you can create a memory-optimized temporal table in a number of ways.
Note
To create memory-optimized tables, you must first create The Memory Optimized Filegroup.
Creating a temporal table with a default history table is a convenient option when you want to control naming and still rely on system to create history table with default configuration. In the example below, a new system-versioned memory-optimized temporal table linked to a new disk-based history table.
CREATE SCHEMA History
GO
CREATE TABLE dbo.Department
(
DepartmentNumber char(10) NOT NULL PRIMARY KEY NONCLUSTERED,
DepartmentName varchar(50) NOT NULL,
ManagerID int NULL,
ParentDepartmentNumber char(10) NULL,
ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
ValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH
(
MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA,
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = History.DepartmentHistory )
);
Creating a temporal table linked to an existing history table is useful when you wish to add system-versioning using an existing table, such as when you wish to migrate a custom temporal solution to built-in support. In the example below, a new temporal table is created linked to an existing history table.
--Existing table
CREATE TABLE Department_History
(
DepartmentNumber char(10) NOT NULL,
DepartmentName varchar(50) NOT NULL,
ManagerID int NULL,
ParentDepartmentNumber char(10) NULL,
ValidFrom datetime2 NOT NULL, ValidTo datetime2 NOT NULL
)
;
--Temporal table
CREATE TABLE Department
(
DepartmentNumber char(10) NOT NULL PRIMARY KEY NONCLUSTERED,
DepartmentName varchar(50) NOT NULL,
ManagerID INT NULL,
ParentDepartmentNumber char(10) NULL,
ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
ValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH
(
SYSTEM_VERSIONING = ON
(
HISTORY_TABLE = dbo.Department_History
, DATA_CONSISTENCY_CHECK = ON
)
, MEMORY_OPTIMIZED = ON
, DURABILITY = SCHEMA_AND_DATA
)
;
See
- System-Versioned Temporal Tables with Memory-Optimized Tables
- Working with Memory-Optimized System-Versioned Temporal Tables
- 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
Povratne informacije
https://aka.ms/ContentUserFeedback.
Uskoro: tokom 2024. postepeno ćemo ukidati probleme s uslugom GitHub kao mehanizam povratnih informacija za sadržaj i zamijeniti ga novim sistemom povratnih informacija. Za više informacija, pogledajtePošalјite i prikažite povratne informacije za