Dataset for demos of how to solve 5 business problems with temporal tables https://medium.com/@bertwagner/5-business-problems-you-can-solve-with-temporal-tables-363255ed3a8c
-- Drop and create our temporal and historical tables | |
IF OBJECT_ID('dbo.CarInventory', 'U') IS NOT NULL | |
BEGIN | |
-- When deleting a temporal table, we need to first turn versioning off | |
ALTER TABLE dbo.CarInventory SET ( SYSTEM_VERSIONING = OFF ) | |
DROP TABLE dbo.CarInventory | |
DROP TABLE dbo.CarInventoryHistory | |
END; | |
CREATE TABLE CarInventory | |
( | |
CarId INT IDENTITY PRIMARY KEY NOT NULL, | |
Year INT, | |
Make VARCHAR(40), | |
Model VARCHAR(40), | |
Color varchar(10), | |
Mileage INT, | |
InLot BIT NOT NULL DEFAULT 1 | |
); | |
CREATE TABLE CarInventoryHistory | |
( | |
CarId INT NOT NULL, | |
Year INT, | |
Make VARCHAR(40), | |
Model VARCHAR(40), | |
Color varchar(10), | |
Mileage INT, | |
InLot BIT NOT NULL, | |
SysStartTime datetime2 NOT NULL, | |
SysEndTime datetime2 NOT NULL | |
); | |
CREATE CLUSTERED INDEX CL_SysStartTime ON dbo.CarInventoryHistory (SysStartTime DESC); | |
GO | |
-- Fill with data | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(1,2017,'Chevy','Malibu','Black',0,1, '2017-05-13 08:00:00.0000000', '2017-05-14 08:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(1,2017,'Chevy','Malibu','Black',0,0, '2017-05-14 08:00:00.0000000', '2017-05-15 07:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(1,2017,'Chevy','Malibu','Black',73,1, '2017-05-15 07:00:00.0000000', '2017-05-16 10:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(1,2017,'Chevy','Malibu','Black',73,0, '2017-05-16 10:00:00.0000000', '2017-05-19 19:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(1,2017,'Chevy','Malibu','Black',349,1, '2017-05-19 19:00:00.0000000', '2017-05-22 10:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(1,2017,'Chevy','Malibu','Black',349,0, '2017-05-22 10:00:00.0000000', '2017-05-23 09:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(1,2017,'Chevy','Malibu','Black',422,1, '2017-05-23 09:00:00.0000000', '2017-05-24 10:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(1,2017,'Chevy','Malibu','Black',422,0, '2017-05-24 10:00:00.0000000', '2017-05-29 18:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(1,2017,'Chevy','Malibu','Black',790,1, '2017-05-29 18:00:00.0000000', '2017-05-30 07:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(1,2017,'Chevy','Malibu','Black',790,0, '2017-05-30 07:00:00.0000000', '2017-06-05 15:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(1,2017,'Chevy','Malibu','Black',1221,1, '2017-06-05 15:00:00.0000000', '2017-06-07 07:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(2,2017,'Chevy','Malibu','Silver',0,1, '2017-05-13 08:00:00.0000000', '2017-05-14 09:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(2,2017,'Chevy','Malibu','Silver',0,0, '2017-05-14 09:00:00.0000000', '2017-05-19 15:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(2,2017,'Chevy','Malibu','Silver',488,1, '2017-05-19 15:00:00.0000000', '2017-05-23 08:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(2,2017,'Chevy','Malibu','Silver',488,0, '2017-05-23 08:00:00.0000000', '2017-05-28 17:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(2,2017,'Chevy','Malibu','Silver',634,1, '2017-05-28 17:00:00.0000000', '2017-06-02 06:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(3,2017,'Chevy','Malibu','Red',0,1,'2017-05-13 8:00:00.0000000','2017-05-14 8:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(3,2017,'Chevy','Malibu','Red',0,0,'2017-05-14 8:00:00.0000000','2017-05-17 7:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(3,2017,'Chevy','Malibu','Red',153,1,'2017-05-17 7:00:00.0000000','2017-05-18 10:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(3,2017,'Chevy','Malibu','Red',153,0,'2017-05-18 10:00:00.0000000','2017-05-19 19:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(3,2017,'Chevy','Malibu','Red',238,1,'2017-05-19 19:00:00.0000000','2017-05-25 10:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(3,2017,'Chevy','Malibu','Red',238,0,'2017-05-25 10:00:00.0000000','2017-05-26 9:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(3,2017,'Chevy','Malibu','Red',305,1,'2017-05-26 9:00:00.0000000','2017-05-28 10:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(3,2017,'Chevy','Malibu','Red',305,0,'2017-05-28 10:00:00.0000000','2017-05-29 18:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(3,2017,'Chevy','Malibu','Red',415,1,'2017-05-29 18:00:00.0000000','2017-06-01 7:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(3,2017,'Chevy','Malibu','Red',415,0,'2017-06-01 7:00:00.0000000','2017-06-03 15:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(3,2017,'Chevy','Malibu','Red',683,1,'2017-06-03 15:00:00.0000000','2017-06-05 7:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(4,2017,'Chevy','Malibu','Blue',0,1,'2017-05-13 8:00:00.0000000','2017-05-14 9:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(4,2017,'Chevy','Malibu','Blue',0,0,'2017-05-14 9:00:00.0000000','2017-05-28 17:00:00.0000000'); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(4,2017,'Chevy','Malibu','Blue',120,1,'2017-05-28 17:00:00.0000000','2017-06-01 18:00:00.0000000'); | |
-- Add our datetime2 columns to be able to turn on system versioning (make it temporal) | |
ALTER TABLE dbo.CarInventory | |
ADD SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL | |
CONSTRAINT DF_SysStart DEFAULT SYSUTCDATETIME(), | |
SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL | |
CONSTRAINT DF_SysEnd DEFAULT '9999-12-31 23:59:59.9999999', | |
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime); | |
GO | |
-- Insert data into our temporal table | |
SET IDENTITY_INSERT dbo.CarInventory ON; | |
INSERT INTO dbo.CarInventory (CarId,Year,Make,Model,Color,Mileage,InLot) VALUES(1,2017,'Chevy','Malibu','Black',1895,1); | |
INSERT INTO dbo.CarInventory (CarId,Year,Make,Model,Color,Mileage,InLot) VALUES(2,2017,'Chevy','Malibu','Silver',888,1); | |
INSERT INTO dbo.CarInventory (CarId,Year,Make,Model,Color,Mileage,InLot) VALUES(3,2017,'Chevy','Malibu','Red',892,1); | |
INSERT INTO dbo.CarInventory (CarId,Year,Make,Model,Color,Mileage,InLot) VALUES(4,2017,'Chevy','Malibu','Blu',200,1); | |
SET IDENTITY_INSERT dbo.CarInventory OFF; | |
GO | |
-- Make sure that the last SysEndTimes in our historical table match the SysStartTimes in our temporal table | |
DECLARE @LastSysStartTimeInTemporalCar1 DATETIME2, @LastSysStartTimeInTemporalCar2 DATETIME2 | |
, @LastSysStartTimeInTemporalCar3 DATETIME2, @LastSysStartTimeInTemporalCar4 DATETIME2 | |
SELECT @LastSysStartTimeInTemporalCar1 = SysStartTime FROM dbo.CarInventory WHERE CarId = 1 | |
SELECT @LastSysStartTimeInTemporalCar2 = SysStartTime FROM dbo.CarInventory WHERE CarId = 2 | |
SELECT @LastSysStartTimeInTemporalCar3 = SysStartTime FROM dbo.CarInventory WHERE CarId = 3 | |
SELECT @LastSysStartTimeInTemporalCar4 = SysStartTime FROM dbo.CarInventory WHERE CarId = 4 | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(1,2017,'Chevy','Malibu','Black',1221,0,'2017-06-08 08:00:00.0000000',@LastSysStartTimeInTemporalCar1); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(2,2017,'Chevy','Malibu','Silver',634,0,'2017-06-09 19:00:00.0000000',@LastSysStartTimeInTemporalCar2); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(3,2017,'Chevy','Malibu','Red',683,0,'2017-06-08 8:00:00.0000000',@LastSysStartTimeInTemporalCar3); | |
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(4,2017,'Chevy','Malibu','Blue',120,0,'2017-06-07 13:00:00.0000000',@LastSysStartTimeInTemporalCar4); | |
GO | |
-- Turn system versionioning/temporal table ON | |
ALTER TABLE dbo.CarInventory SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CarInventoryHistory)); | |
GO | |
-- If everything worked well, we should see our data correctly in these table | |
SELECT * FROM dbo.CarInventory | |
SELECT * FROM dbo.CarInventoryHistory |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment