T-SQL Temporal Tables Demo Script

TemporalTableDemo.sql

use Sandbox
go

-- Create Employee table...
create table dbo.Employee
(
	EmployeeID	int not null primary key clustered,
	EmployeeName	nvarchar(100) not null, 
	JobTitle	nvarchar(50) not null,
	Department	nvarchar(50) not null,
	GroupName	nvarchar(50) not null
)

-- Insert some data from AdventureWorks2014...
insert into dbo.Employee (EmployeeID, EmployeeName, JobTitle, Department, GroupName) 
	select	BusinessEntityID,
		FirstName + ' ' + LastName,
		JobTitle,
		Department,
		GroupName
	from	AdventureWorks2014.HumanResources.vEmployeeDepartment;
go

-- Add period system_time, audit columns (datetime2)  
alter table dbo.Employee 
add 	DateStart datetime2 generated always as row start not null default cast('1900-01-01 00:00:00.0000000' as datetime2), 
	DateEnd	 datetime2 generated always as row end not null default cast('9999-12-31 23:59:59.9999999' as datetime2),
	period for system_time (
		DateStart, 
		DateEnd
	);
go

-- Turn system versioning on and name the history table...
alter table dbo.Employee set (system_versioning = on (history_table = dbo.EmployeeHistory));
go

/*
-- Create system versioned table...
create table dbo.Employee
(
	EmployeeID	int not null primary key clustered,
	EmployeeName	nvarchar(100) not null, 
	JobTitle	nvarchar(50) not null,
	Department	nvarchar(50) not null,
	GroupName	nvarchar(50) not null,
	IsActive	bit not null default 1,
	DateStart	datetime2 generated always as row start not null, 
	DateEnd		datetime2 generated always as row end not null,
	period for system_time (
		DateStart, 
		DateEnd
	)
)
with (system_versioning = on (history_table = dbo.EmployeeHistory));
go

--(history_table = dbo.EmployeeHistory)
alter table dbo.Employee set (system_versioning = off)
go
drop table dbo.Employee
go
--drop table dbo.MSSQL_TemporalHistoryFor_xxxxxxxx
--go
drop table dbo.EmployeeHistory
go
*/

-- The metadata...
select	object_id, temporal_type, temporal_type_desc, history_table_id, name
from	sys.tables
where	object_id = object_id('dbo.Employee', 'U');

select	object_id, temporal_type, temporal_type_desc, history_table_id, name
from	sys.tables
where	object_id = (select history_table_id from sys.tables where object_id = object_id('dbo.Employee', 'U'));	
go

-- Updates for Janice... Promotions!
update	dbo.Employee
set	Department = 'Engineering', 
	JobTitle = 'Design Engineer'
where	EmployeeID = 13;
go

update	dbo.Employee
set	Department = 'Engineering', 
	JobTitle = 'Senior Design Engineer'
where	EmployeeID = 13;
go

update	dbo.Employee
set	Department = 'Engineering', 
	JobTitle = 'Engineering Manager'
where	EmployeeID = 13;
go

-- Dan Wilson great DBA promoted...
update	dbo.Employee
set	JobTitle = 'Information Services Manager'
where	EmployeeID = 271;
go

-- Looking at both tables, current and historical
select * from dbo.Employee where EmployeeId = 13;
select * from dbo.EmployeeHistory where EmployeeID = 13;

select * from dbo.Employee where EmployeeId = 271;
select * from dbo.EmployeeHistory where EmployeeID = 271;

-- Back date some records for querying...
alter table dbo.Employee set (system_versioning = off)

update	dbo.EmployeeHistory
set	DateEnd = '2015-09-05 12:04:07.5683994'
where	EmployeeID = 13 and DateStart = '1900-01-01 00:00:00.0000000'

update	dbo.EmployeeHistory
set	DateStart = '2015-09-05 12:04:07.5683994',
	DateEnd = '2015-09-09 12:04:07.5830984'
where	EmployeeID = 13 and DateStart = '2015-09-16 12:04:07.5683994'

update	dbo.EmployeeHistory
set	DateStart = '2015-09-09 12:04:07.5830984'
where	EmployeeID = 13 and DateStart = '2015-09-16 12:04:07.5830984'

alter table dbo.Employee set (system_versioning = on (history_table = dbo.EmployeeHistory));

-- Queries time travel...
select * from dbo.Employee for system_time as of '2015-09-07' where EmployeeID = 13
select * from dbo.Employee for system_time from '2015-09-08' to '2015-09-15' where EmployeeID = 13
select * from dbo.Employee for system_time between '2015-09-10' and '2015-09-15' where EmployeeID = 13
select * from dbo.Employee for system_time contained in ('2015-09-10', '2015-09-15') where EmployeeID = 13