Redigera

Dela via

Create a memory-optimized system-versioned temporal table

  • Artikel

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 several ways.

To create memory-optimized tables, you must first create the memory optimized filegroup.

Create a memory-optimized temporal table with default history table

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 following example, 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)
);

Create a memory-optimized temporal table with an existing history table

You can create a temporal table linked to an existing history table when you wish to add system-versioning using an existing table. This scenario is useful when you want to migrate a custom temporal solution to built-in support. In the following example, 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
);

Related content