/Create Car Inventory Data.sql
Created Jun 17, 2017
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