Temporal Table

SQL Server 2016 introduces system-versioned temporal tables, which provides information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Temporal table allows you to query updated and deleted data, while a normal table can only return the current data. Temporal table can be used to implement data audit, repairing table data logical corruption made by program bug or human error, and slowly-changing dimensions. Below is an example of creating a temporal table and how to query it in order to perform time-based analysis.

USE [TestDB]
GO

-- Create Table (PK is a must)
CREATE TABLE [dbo].[ProductInventory](
    [ProductID] [int] NOT NULL,
    [LocationID] [smallint] NOT NULL,
    [Shelf] [nvarchar](10) NOT NULL,
    [Bin] [tinyint] NOT NULL,
    [Quantity] [smallint] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ProductInventory_ProductID_LocationID] PRIMARY KEY CLUSTERED
(
    [ProductID] ASC,
    [LocationID] ASC
))
GO

-- Add PERIOD datetime2 columns, non-nullable, optionally HIDDEN them from SELECT * statement
ALTER TABLE dbo.[ProductInventory] ADD
ValidFrom datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_ValidFrom DEFAULT CONVERT(datetime2 (0), '2008-03-31 00:00:00') ,
ValidTo datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_ValidTo DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
GO

-- mark the table as Temporal table, and specify the History Table name
ALTER TABLE dbo.[ProductInventory] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.[ProductInventory_History]));
GO

-- Testing data manipulations
INSERT [dbo].[ProductInventory] (
ProductID,
LocationID,
Shelf,
Bin,
Quantity,
ModifiedDate)
VALUES (
1, 1, 'A', 1, 300, '20160229 16:10:29.303'
);
WAITFOR DELAY '00:00:05'
INSERT [dbo].[ProductInventory] (
ProductID,
LocationID,
Shelf,
Bin,
Quantity,
ModifiedDate)
VALUES (
2, 2, 'B', 2, 6000, '20160229 20:00:00'
);
UPDATE [ProductInventory] SET Quantity = 8 WHERE ProductID = 1
UPDATE [ProductInventory] SET Quantity = 10 WHERE ProductID = 2
DELETE [ProductInventory] WHERE ProductID = 2

-- query
SELECT CAST(SYSUTCDATETIME() AS datetime2(0))
SELECT * FROM [dbo].[ProductInventory]
SELECT * FROM [dbo].[ProductInventory_History]
SELECT *, ValidFrom, ValidTo FROM [dbo].[ProductInventory]
SELECT *, ValidFrom, ValidTo FROM [dbo].[ProductInventory_History]

-- time-based query
SELECT * FROM ProductInventory FOR SYSTEM_TIME AS OF '20170206 10:00';
SELECT * FROM ProductInventory FOR SYSTEM_TIME FROM '20170106 10:00' TO '20170801 00:00';
SELECT * FROM ProductInventory FOR SYSTEM_TIME BETWEEN '20170106 10:00' AND '20170801 00:00';
SELECT * FROM ProductInventory FOR SYSTEM_TIME CONTAINED IN ('20170106 10:00', '20170801 00:00');
SELECT * FROM ProductInventory FOR SYSTEM_TIME ALL;