Have you ever needed to look at what data in a table to look like? used If you have, it probably took a knuckle-cracking filled session of writing group-by statements, nested sub-queries, and window functions to write your time-travelling query. Sorry for your lost day of productivity — I’ve been there too. Fortunately for us, 2016 introduces a new feature to make our point-in-time analysis queries easy to write: temporal tables. SQL Server Temporal Tables? Are Those The Same As Temporary Tables? Don’t let the similar sounding name fool you: “temporal” <> “temporary”. Temporal tables consist of two parts: The — this is the table that contains the current values of your data. temporal table The historical table — this table holds all of the previous values that at some point existed in your temporal table. You might have created a similar setup yourself in previous versions of SQL using triggers. However, using a from this because: temporal table is different You don’t need to write any triggers/stored procedures! All of the history tracking is done automatically by SQL Server. Retrieving the data uses a simple WHERE clause — no complex querying required. I want to make my life easier by using temporal tables! Take my money and show me how! I’m flattered by your offer, but since we are good friends I’ll let you in on these secrets for free. First let’s create a temporal table. I’m thinking about starting up a car rental business, so let’s model it after that: IF OBJECT_ID('dbo.CarInventory', 'U') IS NOT NULLBEGIN-- When deleting a temporal table, we need to first turn versioning offALTER TABLE [dbo].[CarInventory] SET ( SYSTEM_VERSIONING = OFF  )DROP TABLE dbo.CarInventoryDROP TABLE dbo.CarInventoryHistoryENDCREATE TABLE CarInventory(CarId INT IDENTITY PRIMARY KEY,Year INT,Make VARCHAR(40),Model VARCHAR(40),Color varchar(10),Mileage INT,InLot BIT NOT NULL DEFAULT 1,SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime))WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CarInventoryHistory)) The key things to note with our new table above are that it contains a . PRIMARY KEY it contains two fields, marked with . datetime2 GENERATED ALWAYS AS ROW START/END It contains the statement. PERIOD FOR SYSTEM_TIME It contains the property with the (optional) historical table name ( ). SYSTEM_VERSIONING = ON dbo.CarIntventoryHistory If we query our newly created tables, you’ll notice our column layouts are identical: SELECT * FROM dbo.CarInventorySELECT * FROM dbo.CarInventoryHistory Let’s fill it with the choice car of car rental agencies all across the U.S. — the Chevy Malibu: INSERT INTO dbo.CarInventory (Year,Make,Model,Color,Mileage) VALUES(2017,'Chevy','Malibu','Black',0)INSERT INTO dbo.CarInventory (Year,Make,Model,Color,Mileage) VALUES(2017,'Chevy','Malibu','Silver',0) Although we got some unassuming car models, at least we can express our individuality with two different paint colors! In all of the remaining screen shots, the top result is our temporal table and the bottom result is our historical table . dbo.CarInventory dbo.CarInventoryHistory You’ll notice that since we’ve only inserted one row for each our cars, there’s no row history yet and therefore our historical table is empty. Let’s change that by getting some customers and renting out our cars! UPDATE dbo.CarInventory SET InLot = 0 WHERE CarId = 1UPDATE dbo.CarInventory SET InLot = 0 WHERE CarId = 2 Now we see our temporal table at work: we updated the rows in and our historical table was automatically updated with our original values timestamps for how long those rows existed in our table. dbo.CarInventory as well as After a while, our customers return their rental cars: UPDATE dbo.CarInventory SET InLot = 1, Mileage = 73  WHERE CarId = 1UPDATE dbo.CarInventory SET InLot = 1, Mileage = 488 WHERE CarId = 2 It’s totally possible for someone to have driven 73 or 488 miles in a Chevy Malibu in under 4 minutes…ever hear the phrase “drive it like a rental”? Our temporal table show the current state of our rental cars: the customers have returned the cars back to our lot and each car has accumulated some mileage. Our historical table meanwhile got a copy of the rows from our temporal table right before our last statement. It’s automatically keeping track of all of this history for us! UPDATE Continuing on, business is going well at the car rental agency. We get another customer to rent our silver Malibu: UPDATE dbo.CarInventory SET InLot = 0 WHERE CarId = 2 Unfortunately, our second customer gets into a crash and destroys our car: DELETE FROM dbo.CarInventory WHERE CarId = 2 The customer walked away from the crash unscathed; the same can not be said for our profits. With the deletion of our silver Malibu, our test data is complete. Now that we have all of this great historically tracked data, how can we query it? If we want to reminisce about better times when both cars were damage free and we were making money, we can write a query using to show us what our table looked like at that point in the past: SYSTEM_TIME AS OF SELECT*FROMdbo.CarInventoryFOR SYSTEM_TIME AS OF '2017-05-18 23:49:50' The good old days. And if we want to do some more detailed analysis, like what rows have been deleted, we can query both temporal and historical tables normally as well: -- Find the CarIds of cars that have been wrecked and deletedSELECT DISTINCTh.CarId AS DeletedCarIdFROMdbo.CarInventory tRIGHT JOIN dbo.CarInventoryHistory hON t.CarId = h.CarIdWHEREt.CarId IS NULL C̶o̶l̶l̶i̶s̶i̶o̶n̶ Conclusion Even with my car rental business not working out, at least we were able to see how SQL Server’s temporal tables helped us keep track of our car inventory data. I hope you got as excited as I did the first time I saw temporal tables in action, especially when it comes to querying with . Long gone are the days of needing complicated queries to rebuild data for a certain point in time. FOR SYSTEM_TIME AS OF 💚 Enjoyed this post? Please recommend it by giving it a green heart below.

Microsoft

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

How to Use SQL Temporal Tables For Easy Point-In-Time Analysis

Too Long; Didn't Read

Company Mentioned

Bert Wagner

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

How to Use SQL Temporal Tables For Easy Point-In-Time Analysis

Too Long; Didn't Read

Company Mentioned

Bert Wagner

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES