Skip to content

creating-a-memory-optimized-system-versioned-temporal-table.md

Latest commit

 

History

History
91 lines (77 loc) · 4.17 KB

creating-a-memory-optimized-system-versioned-temporal-table.md

File metadata and controls

91 lines (77 loc) · 4.17 KB
title Create a memory-optimized system-versioned temporal table
description Learn how to create a memory-optimized system-versioned temporal table.
author rwestMSFT
ms.author randolphwest
ms.date 07/29/2024
ms.service sql
ms.subservice table-view-index
ms.topic how-to
monikerRange >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current

Create a memory-optimized system-versioned temporal table

[!INCLUDE sqlserver2016-asdbmi]

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.

Note

Memory-optimized temporal tables are only available in [!INCLUDE ssnoversion] and [!INCLUDEssazuremi-md]. Memory-optimized tables and temporal tables are independently available in [!INCLUDEssazure-sqldb].

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