Skip to content

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

Latest commit

 

History

History
92 lines (83 loc) · 4.35 KB

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

File metadata and controls

92 lines (83 loc) · 4.35 KB
title Creating a Memory-Optimized System-Versioned Temporal Table | Microsoft Docs
ms.custom
ms.date 05/05/2016
ms.prod sql
ms.prod_service database-engine, sql-database
ms.reviewer
ms.technology table-view-index
ms.topic conceptual
ms.assetid 1c1fc682-bf5b-4096-a0ff-3235d71c205a
author CarlRabeler
ms.author carlrab
monikerRange =azuresqldb-current||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

Creating a Memory-Optimized System-Versioned Temporal Table

[!INCLUDEtsql-appliesto-ss2016-asdb-xxxx-xxx-md]

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,
      SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
      SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
      PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
   )
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,
      SysStartTime datetime2 NOT NULL, SysEndTime 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,
      SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
      SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
      PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
   )
WITH
   (
      SYSTEM_VERSIONING = ON
         (  
            HISTORY_TABLE = dbo.Department_History
            , DATA_CONSISTENCY_CHECK = ON
         )  
      , MEMORY_OPTIMIZED = ON
      , DURABILITY = SCHEMA_AND_DATA
   )
;

See