Temporal tables in SQL

SQL Server 2016 introduced support for temporal tables (also known as system-versioned temporal tables) as a database feature that brings built-in support for providing 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 is a database feature that was introduced in ANSI SQL 2011.

Real data sources are dynamic and more often than not business decisions rely on insights that analysts can get from data evolution. Use cases for temporal tables include:

·         Auditing all data changes and performing data forensics when necessary

·         Reconstructing state of the data as of any time in the past

·         Calculating trends over time

·         Maintaining a slowly changing dimension for decision support applications

·         Recovering from accidental data changes and application errors

 

-- Create TemporalDB database

CREATE DATABASE TemporalDB

GO

-- Create table

create table dbo.Person

(

    [PersonId] int not null primary key clustered identity(1,1), -- PK must

    [Name] nvarchar(max) not null,

    [Email] nvarchar(max) null,

    [Address] nvarchar(max) null,

    [PhoneNumber] nvarchar(max) null,

    [SysStartTime] datetime2 generated always as row start hidden not null, -- Start datetime must

    [SysEndTime] datetime2 generated always as row end hidden not null, -- End datetime must

    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime) -- Period with start and end datetime must

   )

   WITH (SYSTEM_VERSIONING = ON)-- System_versioning must ON

 

-- Insert records

insert into Person (Name, Email, Address, PhoneNumber) values ('Jeff Winger', 'jwinger@example.com', '1233 Main St', '555-555-5555')

insert into Person (Name, Email, Address, PhoneNumber) values ('Abed Nadir', 'anadir@example.com', '55 W East Rd', '555-555-5555')

insert into Person (Name, Email, Address, PhoneNumber) values ('Shirley Bennet', 'sbennet@example.com', '809 E South Ave', '555-555-5555')

insert into Person (Name, Email, Address, PhoneNumber) values ('Troy Barnes', 'tbone@fake.com', '55 W East Rd', '555-555-5555')

insert into Person (Name, Email, Address, PhoneNumber) values ('Annie Edison', 'aedison@example.com', '55 W East Rd', '555-555-5555')

insert into Person (Name, Email, Address, PhoneNumber) values ('Pierce Hawthorne', 'phawthorne@example.com', '123 N Fake Blvd', '555-555-5555')

insert into Person (Name, Email, Address, PhoneNumber) values ('Britta Perry', 'bperry@example.com', '9985 Main St', '555-555-5555')

-- Get records from table

select * from Person

update Person set PhoneNumber = '555-123-4567' where PersonId = 1

update Person set Email = 'jwinger@wingerlaw.com' where PersonId = 1

update Person set PhoneNumber = '555-999-2222' where PersonId = 2

update Person set Address = null where PersonId = 6

SELECT * FROM [TemporalDB].[dbo].[MSSQL_TemporalHistoryFor_581577110]

-- Pause for a few minutes to make later queries easier.

update Person set Email = 'sbennet@greendalecc.com' where PersonId = 4

SELECT * FROM [TemporalDB].[dbo].[MSSQL_TemporalHistoryFor_581577110]

-- Pause for a few minutes to make later queries easier.

insert into Person (Name) values ('Ben Chang')

SELECT * FROM [TemporalDB].[dbo].[MSSQL_TemporalHistoryFor_581577110]

-- Pause for a few minutes to make later queries easier.

delete from Person where PersonId = 8

SELECT * FROM [TemporalDB].[dbo].[MSSQL_TemporalHistoryFor_581577110]