不使用系統時間的臨時表

October 16, 2018

我們想在 SQL Server 2016 中實現時態表。我們正在創建一個數據倉庫並開發類型 2 漸變維度表。
對於 BeginDate，我們希望它依賴於交易日期而不是目前的 getdate 時間。我們正在重新處理交易歷史。下面的範例，客戶具有健身房或銀行狀態，並根據交易日期從非活動狀態變為活動狀態或待處理狀態。
我們目前有這個。
CREATE TABLE dbo.Department
(
   CustomerId int primary key,
   MembershipStatus int,
   TransactionDate datetime
);
我們想創建一個這樣的表。
CREATE TABLE dbo.DepartmentHistory
(
   CustomerId int primary key,
   MembershipStatus int,
   TransactionDate datetime,
   BeginDatetime datetime,
   EndDatettime datetime
);
範例用法是：
2018 年 3 月 5 日的第一筆客戶交易為待處理 P
+------------+--------+-----------+---------+
| 客戶 ID | 狀態 | 開始日期 | 結束日期 |
+------------+--------+-----------+---------+
| 1 | 磷 | 2018 年 3 月 5 日 | 空 |
+------------+--------+-----------+---------+
第二筆交易是 4/21/2018 是 Active A
+------------+--------+-----------+-----------+
| 客戶 ID | 狀態 | 開始日期 | 結束日期 |
+------------+--------+-----------+-----------+
| 1 | 磷 | 2018 年 3 月 5 日 | 2018 年 4 月 21 日 |
| 1 | 一個 | 2018 年 4 月 21 日 | 空 |
+------------+--------+-----------+-----------+

臨時表是系統版本的1 ，因此“手動設置”給定歷史記錄行的時間戳的唯一方法是在修改該行時更改作業系統時間……您可能不想這樣做。
如果您想在歷史表上“手動設置”時間界限，以便它支持時態查詢語法，您可以通過在現有數據之上手動應用時態表來實現。這有點棘手，需要基礎數據符合時間歷史版本控制規則。當引入 Temporal Tables 時，Google充斥著各種關於此的部落格文章；因為我有一段時間沒有玩過那個確切的案例了，所以現在我將任意連結到這個看起來很有希望的例子。
快速展示
請記住，系統版本化的臨時表實際上是兩個表——一個“現在”表和一個“歷史”表壓縮在一起……
create table dbo.b_now ( 
    i int not null primary key 
   ,info varchar(10)
   ,start_dt datetime2 generated always as row start
   ,end_dt   datetime2 generated always as row end
   ,period for system_time (start_dt, end_dt)
) with (system_versioning = on (history_table = dbo.b_history));
go

insert b_now (i,info)
values 
(1,'AAA')
,(2,'BBB');
go
update b_now set info = 'XXX' where i = 1
go
select * from b_history
select * from b_now
go
請注意，雖然b_now似乎是“基表”並支持時間語法 - 它只是一個單獨的對象。刪除對象之間的系統版本綁定加強了這一點。
使用此資訊“手動版本”
記住這一點 - 並記住我們可以來回切換，我們SYSTEM_VERSIONING可以告訴 SQL Server 任意應用（並且任意忽略）系統版本控制規則到任意一對對象，只要……ON``OFF``ON
模式匹配
數據完全符合系統版本化的情況
但我想要範常式式碼！
是的，我想你可能會 - 所以試試這個……
drop table if exists a_now ,a_history;
go
create table a_now ( 
    i    int not null primary key
   ,info varchar(10)
   ,start_dt datetime2 not null
   ,end_dt   datetime2 not null 
       -- you'll want this CHECK later...
       check (end_dt = convert(datetime2,'9999-12-31 23:59:59.9999999'))
);
go
create table a_history (
    i        int not null
   ,info     varchar(10)
   ,start_dt datetime2 not null
   ,end_dt   datetime2 not null
);
go

declare @end_of_time datetime2 = '9999-12-31 23:59:59.9999999';

insert a_now values 
(1,'XXX','2017-01-01',@end_of_time)
,(2,'BBB','2017-01-01',@end_of_time)

insert a_history values
(1,'AAA','2016-01-01','2017-01-01');
go
select * from a_now
select * from a_history
…看起來很像b_nowand b_history，不是嗎？太糟糕了，它不是一個合適的系統表……
alter table a_now
   add period for system_time (start_dt, end_dt);
go
alter table a_now
   set (system_versioning = on (history_table = dbo.a_history));
go
等等……？您甚至可以在“ now ”表的頂部放置一個視圖來隱藏您對命名約定的濫用。
create or alter view a 
as
select * from a_now
go

select * 
from a 
for system_time as of '2016-06-01'
好的，是的…但是我如何添加新行 Smarty Pants 先生？
非常小心……就是這樣。
alter table a_now
   set (system_versioning = off);
alter table a_now
   drop period for system_time;
go

declare @end_of_time datetime2 = '9999-12-31 23:59:59.9999999';

insert a_now values 
(3,'CCC','2018-01-01',@end_of_time);

update a_now set 
    start_dt = '2018-01-01'
   ,info = 'YYYY'
where i = 1;

insert a_history 
values 
 (1,'XXX','2017-01-01','2018-01-01')

alter table a_now
   add period for system_time (start_dt, end_dt);
go
alter table a_now
   set (system_versioning = on (history_table = dbo.a_history));
go

select * from a for system_time as of '2017-06-01'
我把它留給讀者（你）將//邏輯和相關的INSERT/ re -system版本包裝成一個可重用的模組。UPDATE``DELETE``DROP``SET
1. 注意，這是在創建時態表所需的 DDL 中period for SYSTEM_time的語法暗示的