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.