SQL Server 2016: temporal table with In-Memory

In SQL Server 2016 CTP 3.0, temporal tables with In-Memory OLTP is supported. 🙂
At the JSS2015 (Journée SQL Server 2015), I have presented a little demo about temporal table with a table In-Memory.
This article is a complement of my presentation and a good add to my series of Temporal Table.

My first step is to create the temporal In-Memory table:

CREATE TABLE [dbo].[Animals2]
(
	[AnimalId] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](200) COLLATE French_CI_AS NOT NULL,
	[Genus Species] [varchar](200) COLLATE French_CI_AS NOT NULL,
	[Number] [int] NOT NULL,
	[StartDate] [datetime2](0) GENERATED ALWAYS AS ROW START NOT NULL,
	[EndDate] [datetime2](0) GENERATED ALWAYS AS ROW END NOT NULL,
	PERIOD FOR SYSTEM_TIME ([StartDate], [EndDate]),

 CONSTRAINT [Animals_primaryKey]  PRIMARY KEY NONCLUSTERED HASH 
(
	[AnimalId]
)WITH ( BUCKET_COUNT = 131072)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA, 
SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[AnimalsHistory2] , DATA_CONSISTENCY_CHECK = ON ) )

GO

The first ascertainment in the sys.tables is that the History table is not In-Memory.
Only the System-Versioned table is in In-Memory.
The query to have the result:

SELECT name,object_id,is_memory_optimized,durability,temporal_type,temporal_type_desc FROM sys.tables

I insert a value in the table and read the table:

INSERT INTO [Zoo].[dbo].[Animals2]([Name],[Genus Species],[Number])
     VALUES('Black swan','Cygnus atratus',12)

SELECT * FROM [Zoo].[dbo].[Animals2]

The value is present in the table and now I read the History table (who is empty of course)

This error message comes from:

Msg 13582, Level 16, State 1, Line 7
Table ‘Zoo.dbo.AnimalsHistory2’ is history table for in-memory system-versioned temporal table and cannot be queried.
I cannot read the history table and my first thing was to run some updates to read the history table with values.

It is impossible to ask directly the History table and the only way is to use FOR SYSTEM_TIME ALL like this to have the History:

SELECT *
  FROM [Zoo].[dbo].[Animals2] FOR SYSTEM_TIME ALL WHERE Name='Black swan'

In the execution plan, the insert to the history table is not the same as a “classical” temporal table

The schema of a “classical” temporal architecture is the current table and the history table.

But in an In-Memory mode, the temporal architecture introduces an Internal In-Memory History Table. This table is a “buffer table” In-Memory to not disturb the speed of the Temporal In-Memory table

This Internal In-Memory History Table is called memory_optimized_history_table_”temporalTableId”

To transfer values from Internal In-Memory History table to the History table, the stored procedure sp_xtp_flush_temporal_history can be used like this:

exec sys.sp_xtp_flush_temporal_history [dbo], [Animals2]

The only way to read the history table is to disable the temporal mode.

ALTER TABLE [Zoo].[dbo].[Animals2] SET (SYSTEM_VERSIONING = OFF) 
GO
SELECT * FROM [Zoo].[dbo].[AnimalsHistory2]