RiCo技術農場

使用新的FOR SYSTEM_TIME 子句，並搭配四個次子句查詢目前和歷史資料表之間資料。

 

FROM <start_date_time> TO <end_date_time>:查詢特定的時間範圍的資料

SELECT * FROM dbo.MyTemporal
FOR SYSTEM_TIME from '2016-12-04 12:00:00' to '2016-12-06 12:00:00'

透過文字執行計畫，清楚知道篩選條件為

[StartTime] < end_date_time and [EndTime]> start_date_time

 

BETWEEN <start_date_time> AND <end_date_time>:查詢特定的時間範圍的資料

SELECT * FROM dbo.MyTemporal
FOR SYSTEM_TIME BETWEEN '2016-12-05 22:00:00' AND '2016-12-05 22:42:53'

透過文字執行計畫，清楚知道篩選條件為

[StartTime]<= end_date_time and [EndTime]> start_date_time

 

CONTAINED IN (<start_date_time> , <end_date_time>):只傳回存在於指定期間內的資料

SELECT * FROM dbo.MyTemporal
FOR SYSTEM_TIME CONTAINED IN ('2016-12-01', '2016-12-05 23:00:00')

透過文字執行計畫，清楚知道篩選條件為

[StartTime]>= start_date_time  and [EndTime]<= end_date_time

 

ALL:查詢沒有限制

SELECT * FROM dbo.MyTemporal
FOR SYSTEM_TIME ALL

透過文字執行計畫，清楚知道篩選條件為

[StartTime]<>[EndTime]

 

AS OF <date_time>: 查詢特定的時間

SELECT * FROM dbo.MyTemporal
FOR SYSTEM_TIME AS OF '2016-12-06 13:45:00';

透過文字執行計畫，清楚知道篩選條件為

[StartTime]<= date_time AND [EndTime] >date_time

 

正常資料表轉換為時態表

--正常資料表
create table mytest
(
ID int identity(1,1) not null primary key clustered
,Name varchar(20) not null
)
insert into mytest values('rico'),('sherry')

--新增時間欄位 not null
ALTER TABLE dbo.mytest
ADD StartTime DATETIME2 GENERATED ALWAYS AS ROW START
   DEFAULT GETUTCDATE() ,--HIDDEN,
 EndTime  DATETIME2 GENERATED ALWAYS AS ROW END
   DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
 PERIOD FOR SYSTEM_TIME (StartTime, EndTime)

 --啟用時態表 
ALTER TABLE dbo.mytest
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.mytest_Archive))

正常資料表和歷史資料表轉換為時態表

create table mytestB
(
ID int identity(1,1) not null primary key clustered
,Name varchar(20) not null
,[StartTime] DATETIME NOT NULL
,[EndTime] DATETIME NOT NULL
)
insert into mytestB values('rico','2016-12-01','2016-12-02'),('sherry','2016-12-03','2016-12-05')

---正常歷史資料表
CREATE TABLE [dbo].[mytestB_Archive]
(
    [Serial] int IDENTITY(1,1) NOT NULL primary key clustered ,
    [ID] int NOT NULL,
    [Name] varchar(20) NOT NULL,
    [StartTime] DATETIME NOT NULL,
    [EndTime] DATETIME NOT NULL     
) 



--Alter StartTime Column type to DATETIME2
ALTER TABLE dbo.mytestB
ALTER COLUMN  [StartTime] DATETIME2 NOT NULL
--Alter EndTime Column type to DATETIME2
ALTER TABLE dbo.mytestB
ALTER COLUMN  [EndTime] DATETIME2 NOT NULL
GO
--max datetime
update mytestB set EndTime ='9999-12-31 23:59:59.9999999'

--Add the period definition
ALTER TABLE dbo.mytestB
ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)

--Alter StartTime Column type to DATETIME2
ALTER TABLE dbo.mytestB_Archive
ALTER COLUMN  [StartTime] DATETIME2 NOT NULL
--Alter EndTime Column type to DATETIME2
ALTER TABLE dbo.mytestB_Archive
ALTER COLUMN  [EndTime] DATETIME2 NOT NULL
GO

--DROP CONSTRAINT
ALTER TABLE mytestB_Archive
DROP CONSTRAINT [PK__mytestB___1A00E09299C6491D]

--drop IDENTITY column
ALTER TABLE mytestB_Archive
DROP COLUMN [Serial]

--enable System-Versioning
ALTER TABLE dbo.mytestB
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.mytestB_Archive) )

 

Enjoy SQL Server 2016

 

參考

Querying Data in a System-Versioned Temporal Table

Stopping System-Versioning on a System-Versioned Temporal Table