Skip to content

Instantly share code, notes, and snippets.

@bertwagner

bertwagner/Create Car Inventory Data.sql

Created June 17, 2017 11:50
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save bertwagner/4793588e2ff047d23c8211fb472028b1 to your computer and use it in GitHub Desktop.
Save bertwagner/4793588e2ff047d23c8211fb472028b1 to your computer and use it in GitHub Desktop.
Download ZIP
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
Raw
Create Car Inventory Data.sql
-- 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