SYSTEM_VERSIONING (Temporal Tables)

What is a system-versioned temporal table?
A system-versioned temporal table is a type of user table designed to keep a full history of data changes and allow easy point in time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system

(Quelle: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables)

Beispiel:

SELECT *
FROM Employee
FOR SYSTEM_TIME
BETWEEN '2014-01-01 00:00:00.0000000'
AND '2015-01-01 00:00:00.0000000'

Video: https://channel9.msdn.com/Shows/Data-Exposed/Temporal-in-SQL-Server-2016

Weitere sehr gute Beschreibung: http://sqlhints.com/tag/for-system_time-as-of/

CREATE DATABASE SqlHintsQueryingTemporalDB
GO
USE SqlHintsQueryingTemporalDB
GO
CREATE TABLE dbo.Customer 
(  
  Id INT NOT NULL PRIMARY KEY CLUSTERED,
  Name NVARCHAR(100) NOT NULL, 
  StartTime DATETIME2 GENERATED ALWAYS AS ROW START
            HIDDEN NOT NULL,
  EndTime   DATETIME2 GENERATED ALWAYS AS ROW END
            HIDDEN NOT NULL,
  PERIOD FOR SYSTEM_TIME (StartTime, EndTime)   
) 
WITH(SYSTEM_VERSIONING= ON (HISTORY_TABLE=dbo.CustomerHistory))



INSERT INTO dbo.Customer VALUES (1,'Basavaraj')
GO
WAITFOR DELAY '00:02:00'
GO
UPDATE dbo.Customer SET Name = 'Basavaraj Biradar' WHERE Id = 1
GO
WAITFOR DELAY '00:02:00'
GO
UPDATE dbo.Customer SET Name = 'Basavaraj P Biradar'
WHERE Id = 1
GO
WAITFOR DELAY '00:02:00'
GO
UPDATE dbo.Customer SET Name = 'Basavaraj Prabhu Biradar'
WHERE Id = 1
GO

Select *, StartTime, EndTime from dbo.Customer
Select *, StartTime, EndTime from dbo.CustomerHistory
Select *, StartTime, EndTime from dbo.Customer FOR SYSTEM_TIME ALL order by StartTime asc
--Select *, StartTime, EndTime from dbo.Customer FOR SYSTEM_TIME AS OF '2018-03-01'
Select *, StartTime, EndTime from dbo.Customer FOR SYSTEM_TIME AS OF '2018-03-08 10:34:00'
--Select *, StartTime, EndTime from dbo.Customer FOR SYSTEM_TIME AS OF '2018-04-01'
Select *, StartTime, EndTime from dbo.Customer FOR SYSTEM_TIME AS OF '2019'



Select *, StartTime, EndTime from dbo.Customer FOR SYSTEM_TIME ALL order by StartTime asc
DECLARE @StartDateTime DATETIME2 = '2018-03-08 10:35:01.2131440'
DECLARE @EndDateTime DATETIME2   = '2018-03-08 10:39:01.2463151'
--Select * from (SELECT Id, Name, StartTime, EndTime FROM dbo.Customer union SELECT Id, Name, StartTime, EndTime FROM dbo.CustomerHistory ) t 
--WHERE StartTime >=@StartDateTime AND EndTime <= @EndDateTime 

--SELECT Id, Name, StartTime, EndTime FROM dbo.Customer FOR SYSTEM_TIME BETWEEN @StartDateTime AND @EndDateTime 
--SELECT Id, Name, StartTime, EndTime FROM dbo.Customer FOR SYSTEM_TIME FROM @StartDateTime TO @EndDateTime 
SELECT Id, Name, StartTime, EndTime FROM dbo.Customer FOR SYSTEM_TIME CONTAINED IN ( @StartDateTime , @EndDateTime  )
Select * from (SELECT Id, Name, StartTime, EndTime FROM dbo.Customer union SELECT Id, Name, StartTime, EndTime FROM dbo.CustomerHistory ) t
WHERE StartTime <= @EndDateTime AND EndTime >= @StartDateTime

Quelle:

http://sqlhints.com/2015/12/31/temporal-tables-in-sql-server-2016-part-2-querying-system-versioned-temporal-table/