1

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).

3
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.