ZQcqATJP

1CREATE TABLE dbo.Department
2(
3    CustomerId int primary key,
4    MembershipStatus int,
5    TransactionDate datetime
6 );
7	
8CREATE TABLE dbo.DepartmentHistory
9(
10    CustomerId int primary key,
11    MembershipStatus int,
12    TransactionDate datetime,
13    BeginDatetime datetime,
14    EndDatettime datetime
15 );
16	
17CREATE TABLE
18	
19CREATE TABLE
20	
21create table dbo.b_now ( 
22     i int not null primary key 
23    ,info varchar(10)
24    ,start_dt datetime2 generated always as row start
25    ,end_dt   datetime2 generated always as row end
26    ,period for system_time (start_dt, end_dt)
27) with (system_versioning = on (history_table = dbo.b_history));
28go
29
30insert b_now (i,info)
31values 
32 (1,'AAA')
33,(2,'BBB');
34go
35update b_now set info = 'XXX' where i = 1
36go
37select * from b_history
38select * from b_now
39go
40	
41drop table if exists a_now ,a_history;
42go
43create table a_now ( 
44     i    int not null primary key
45    ,info varchar(10)
46    ,start_dt datetime2 not null
47    ,end_dt   datetime2 not null 
48        -- you'll want this CHECK later...
49        check (end_dt = convert(datetime2,'9999-12-31 23:59:59.9999999'))
50);
51go
52create table a_history (
53     i        int not null
54    ,info     varchar(10)
55    ,start_dt datetime2 not null
56    ,end_dt   datetime2 not null
57);
58go
59
60declare @end_of_time datetime2 = '9999-12-31 23:59:59.9999999';
61
62insert a_now values 
63 (1,'XXX','2017-01-01',@end_of_time)
64,(2,'BBB','2017-01-01',@end_of_time)
65
66insert a_history values
67 (1,'AAA','2016-01-01','2017-01-01');
68go
69select * from a_now
70select * from a_history
71	
72alter table a_now
73    add period for system_time (start_dt, end_dt);
74go
75alter table a_now
76    set (system_versioning = on (history_table = dbo.a_history));
77go
78	
79create or alter view a 
80as
81select * from a_now
82go
83
84select * 
85from a 
86for system_time as of '2016-06-01'
87	
88alter table a_now
89    set (system_versioning = off);
90alter table a_now
91    drop period for system_time;
92go
93
94declare @end_of_time datetime2 = '9999-12-31 23:59:59.9999999';
95
96insert a_now values 
97 (3,'CCC','2018-01-01',@end_of_time);
98
99update a_now set 
100     start_dt = '2018-01-01'
101    ,info = 'YYYY'
102where i = 1;
103
104insert a_history 
105values 
106  (1,'XXX','2017-01-01','2018-01-01')
107
108alter table a_now
109    add period for system_time (start_dt, end_dt);
110go
111alter table a_now
112    set (system_versioning = on (history_table = dbo.a_history));
113go
114
115select * from a for system_time as of '2017-06-01'