SQL Server 2016 – Create Partition for Temporal Table Based on Period

partitioningsql-server-2016temporal-tables

To improve the performance of my ETL, I decided to use temporal table instead of a slowly changing dimension.

For my Employee dimension, I created a temporal table. Now I want to split data into partitions based on periods.

I already declared StartTimeV and EndTimeV (the created columns for my temporal tables) as system period start time and end time periods. I want to create a partition for my temporal table based on the period (StartTimeV and EndTimeV).

Best Answer

I mean you can... but I don't think you necessarily should...

Given a setup of the form...

use [master]
create database ynot;
alter authorization on database ::ynot to sa;
go
use ynot
go
create table dbo.foo ( 
     id         int identity primary key
    ,info       varchar(100)
    ,StartTimeV datetime2(0) generated always as row start
    ,EndTimeV   datetime2(0) generated always as row end
    ,period for system_time (StartTimeV, EndTimeV)
) 
with (system_versioning = on (history_table = dbo.foo_hist));

You can...

  • Remove the system versioning
  • Apply partitioning to the underlying history table
  • Reapply system versioning

...in this way

alter table dbo.foo set (system_versioning = off);
go
create partition function pf_months(datetime2(0)) 
    as range right for values (
         '20190501'
        ,'20190601'
        ,'20190701'
        ,'20190801'
        ,'20190901'
        ,'20191001'
        ,'20191101'
        ,'20191201'
);
go
create partition scheme ps_months 
    as partition pf_months 
    all to ([PRIMARY]);
go
alter table dbo.foo set (system_versioning = off);
go
drop table dbo.foo_hist;
go
create table dbo.foo_hist (
    id         int          not null,
    info       varchar(100) null,
    StartTimeV datetime2(0) not null,
    EndTimeV   datetime2(0) not null
)
on ps_months([StartTimeV]);
go
alter table dbo.foo 
    set (system_versioning = on (history_table = dbo.foo_hist));
go

It works as-is, although I won't speak to interoperability, scaling, or storage management concerns at this time.

insert foo (info )
values ('stuff')
      ,('things');
go
update foo set
    info = 'objects'
where id = 1;

delete foo where id = 2;

select * from foo for system_time all;
go

Demo cleanup

use [master]
drop database ynot;
go
Related Question