Create table partitions on different filegroups, the first boundary on a Memory Optimised filegroup

Question

99% of all our transactions are against the latest week's data, a WEEK_SELECTOR column describing which week is being described. Partitioning our billion+ row table by week would be beneficial, more so if the half million or so rows for the latest week were in an in-memory partition.

On attempting to split the top week off to our memory optimised filegroup with

CREATE PARTITION FUNCTION [TPF](int) AS RANGE RIGHT FOR VALUES (N'1700114')
CREATE PARTITION SCHEME [TPS] AS PARTITION [TPF] TO ([PRIMARY], [memtest])

we get the following error:

Msg 7737, Level 16, State 1, Line 2
Filegroup memtest is of a different filegroup type than the first filegroup in partition scheme TPS

We are now assuming that filegroups must be of the same type for partitions and our plan is not possible, but cannot find documentation to say that it is impossible.

Can someone explain how it can be done, or if not, provide a link saying as much? Thanks.

Answer 1

Partitioning memory-optimized tables directly is not yet supported. See SQL Server Features Not Supported for In-Memory OLTP.

A workaround is described in the documentation Application Pattern for Partitioning Memory-Optimized Tables

You can emulate partitioned tables with memory-optimized tables by maintaining a partitioned table and a memory-optimized table with a common schema. Current data would be inserted and updated in the memory-optimized table, while less-frequently accessed data would be maintained in the traditional partitioned table.

Alternatively, you could consider other technology solutions like Columnstore for real time operational analytics, which combines in-memory OLTP with columnstore (as [optionally filtered] secondary storage).