Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
This script contains the examples demonstrating the usage of Temporal Tables in SQL Server 2016.
-- Creating Temporal table with automatically named History table
CREATE TABLE dbo.Employee
(
EmployeeID int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
EmployeeName varchar(50) NOT NULL,
Age int NULL,
Email nvarchar(50),
ValidFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (
ValidFrom,
ValidTo
)
)
WITH ( SYSTEM_VERSIONING = ON );
GO
---------------------------------------------------------------------------
SELECT * FROM dbo.Employee
---------------------------------------------------------------------------
-- To drop the temporal table, the System Versioning needs to be turned off
ALTER TABLE [dbo].[Employee] SET ( SYSTEM_VERSIONING = OFF )
GO
DROP TABLE [dbo].[Employee]
GO
DROP TABLE [dbo].[MSSQL_TemporalHistoryFor_441768631]
GO
--------------------------------------------------------------------------
-- Creating Temporal table with a named History table
CREATE TABLE dbo.Employee
(
EmployeeID int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
EmployeeName varchar(50) NOT NULL,
Age int NULL,
Email nvarchar(50),
ValidFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (
ValidFrom,
ValidTo
)
)
WITH ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory)); -- Named History table
GO
----------------------------------------------------------------------------
-- Insert data into Temporal table
INSERT INTO dbo.Employee
(EmployeeName, Age, Email)
VALUES
('Samir', 30, N'samirbehara@gmail.com'),
('Dennis', 40, 'dennisbaker@hotmail.com'),
('Robert', 35, 'robert.tedrow@yahoo.com')
SELECT * FROM dbo.Employee
SELECT * FROM dbo.EmployeeHistory
----------------------------------------------------------------------------
-- Modify the Base table and look at the updates to the History table
DELETE FROM dbo.Employee
WHERE EmployeeID = 3
UPDATE dbo.Employee
SET Age = 38
WHERE EmployeeID = 2
SELECT * FROM dbo.Employee
SELECT * FROM dbo.EmployeeHistory
----------------------------------------------------------------------------
-- Look at the current state of the table
SELECT * FROM dbo.Employee
-- Reconstruct state of data at a specific time using the AS OF clause
SELECT * FROM dbo.Employee
FOR SYSTEM_TIME AS OF '2016-05-04 02:34:09.9419923'
------------------------------------------------------------------------------
SELECT * FROM dbo.Employee
WHERE EmployeeID = 2
-- Get all historical data for a specific row
SELECT * FROM dbo.Employee
FOR SYSTEM_TIME AS OF '2016-05-04 02:34:09.9419923'
WHERE EmployeeID = 2
-------------------------------------------------------------------------------
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.