Sql Server In memory OLTP table for System Versioned Tables

Question

I have been reading about the In Memory OLTP Tables in SQL Server 2016 and would like to try it out.

However, all of the tables identified as High gain/Low migration effort are system-versioned tables. When I right click on a system-versioned table, I don't see the option to enable it for Memory optimization, but I do see the same on a non-system versioned table.

I read that it is possible to keep the history tables in the disk and only migrate the main table to the memory. Has anyone done this? Or is there any link I missed that describes how this can be accomplished?

Answer 1

Let's say you have in-memory table and some data in it:

CREATE TABLE [dbo].[StackOverflow]
(
    [ID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024)
   ,[Name] VARCHAR(128) NOT NULL
 ) WITH (MEMORY_OPTIMIZED = ON)
GO

INSERT INTO [dbo].[StackOverflow] ([ID], [Name])
VALUES (1, 'Raul')
      ,(2, 'Mike');

GO

Then, we are going to make it temporal but the history table will be specified as normal table:

ALTER TABLE [dbo].[StackOverflow]   
ADD [SysStartTime] DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT [DF_StackOverflow_SysStart] DEFAULT SYSUTCDATETIME()  
   ,[SysEndTime] DATETIME2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT [DF_StackOverflow_SysEnd] DEFAULT CONVERT(DATETIME2 (0), '9999-12-31 23:59:59')
   ,PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]);   
GO

CREATE TABLE [dbo].[StackOverflowLog]
(
    [ID] INT NOT NULL
   ,[Name] VARCHAR(128) NOT NULL
   ,[SysStartTime] DATETIME2(0) NOT NULL
   ,[SysEndTime] DATETIME2(0) NOT NULL
 )
GO

ALTER TABLE [dbo].[StackOverflow]   
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[StackOverflowLog])); 

Add some new records, modify existing ones:

UPDATE [dbo].[StackOverflow]
SET [Name] = 'Zidane'
WHERE [ID] = 1;

INSERT INTO [dbo].[StackOverflow] ([ID], [Name])
VALUES (3, 'Tervel');

Check what's going on:

SELECT *
     ,[SysStartTime]
     ,[SysEndTime]
FROM [dbo].[StackOverflow] FOR SYSTEM_TIME ALL
ORDER BY [ID], [SysStartTime];

You can use the following query to confirm your log table is not in the memory:

SELECT T.[name]
      ,T.[is_memory_optimized]
FROM [sys].[tables] T
WHERE T.[name] IN ('StackOverflow', 'StackOverflowLog')
    AND T.[schema_id] = SCHEMA_ID('dbo');

Clear the demonstration objects, now:

DROP TABLE IF EXISTS [dbo].[StackOverflowLog];
DROP TABLE IF EXISTS [dbo].[StackOverflow];

Good luck.