From the course: Developing Microsoft SQL Server 2016 Databases

Go back in time with temporal tables - SQL Server Tutorial

From the course: Developing Microsoft SQL Server 2016 Databases

Start my 1-month free trial Buy for my team

Go back in time with temporal tables

“

- [Instructor] One of the most exciting new features added to SQL Server 2016 is support for storing historical data. This comes in the form of Temporal Tables, where the history of each record is tracked across updates, and you can retrieve the status of any record as it existed in any point in time, using standard T SQL commands. This is a very useful feature, since tables can now track the evolution of data to get the full picture of its history. Temporal Tables seamlessly copy and time-stamp records into a history table any time there's an insert, update or delete. Let's see how they work by creating a new Temporal Tables database. I'll go ahead and highlight lines two through five in our script and execute those to create the new database. Then, we'll scroll down just a little bit and we'll create a new Temporal Table. A Temporal Table is created just like any other table with a Create Table statement. In our case, it's going to be called dbo.Products. It'll have a couple of different fields. The first one, Product ID, will be an identity field in the primary key. We'll have a product name that'll store nvarcher 50 datatypes, and we'll have a product price that'll use these small money datatype. The next three lines are what actually create the Temporal Table. We'll have a field called Valid From. It'll store a datetime2 datatype, and it has this line that says, generated always as row start. Then we'll have a ValidTo line. It'll also use the datetime2 datatype and it'll say generated always as row end. Finally, we'll have period for system time, and, then, in parentheses, ValidFrom comma ValidTo, which just references the other two columns we just created. Finally, at the end of that we'll have this with clause which says System Versioning equals On. Those lines together will convert a standard table into a Temporal Table. Let's go ahead and highlight lines eight through 17, and execute them, and that'll complete the command successfully, and now we have our Temporal Table database. Next, it's time to add some values to our table. Let's go ahead and scroll down a little bit. We're going to insert some values into the Products Table, into specifically the Product Name and Product Price fields. And we'll add Mixed Nuts at 3.99, shelled peanuts at 5.49 and roasted almonds at 7.29. I'll highlight lines 20 through 24 and execute those. Then, we can scroll down just a little bit further and run the Select Statement in order to see the current state of our data. Notice that the results shows the ValidFrom and ValidTo columns and includes the exact date that those records were created and when they're valid to. Essentially, the end of time. If you want to review the current system time, run this line here, that says Select GetUTCDate. Go ahead and run that, and we'll see the current date and time of our current system. I'm actually going to copy this time to my clipboard. I'll just select it and press control + C on my keyboard. And then, just so I have it handy, I'm going to come down here and I'll just add it in as a comment below that line. Next, let's update one of our prices. I'll scroll down here, and we'll run this update statement. We're going to update the Products Table, and we're going to set the Product Price to 6.99 where the Product Name is Roasted Almonds. Essentially, the roasted almonds are getting a reduction in price. I'll highlight lines 36 through 39, and execute them. Now, if I want to see the current state, I'll just run the standard Select * from the dbo.Products table and we'll see the current state, or the current price of our roasted almonds at 6.99. And if we take a look at the ValidFrom column, we can see that the roasted almonds has a different ValidFrom time stamp. It was created about a minute after these other two products. Now, here's where the power of Temporal Tables comes into play. Let's go ahead and scroll down a little bit further. If I want to see the historic pricing from a few moments ago, we'll just run a different Select statement. We'll say Select * from dbo Products for a system time as of, and then we'll insert the time that we're interested in. In my case, I'm just going to paste from my clipboard the same time that we got just a moment ago. For me, that's going to be the first of November 2016 at 8:49:00. I'll run this. We'll run this and see what the price of our products used to be at that point in time. And you can see that roasted almonds were 7.29 back then. If I want to see the full pricing history of our almonds, from the very beginning when they were created all the way 'til the current date, let's scroll down just a little bit further, and we can run this Select statement. It'll select the same records from the dbo.Products table, for a system time between, and then an early starting date. In my case, I'm just going to use January 1, 2000, and an ending date that's way in the future. Essentially, again, the end of time. Then, we'll specify a Where clause. I'm only interested in the roasted almonds products. And, finally, we'll sort the records out based off of the ValidFrom column. Let's go and highlight these rows and execute them. Now I can see that my roasted almonds have gone through two different prices. They were 7.29 from this date to this date, and then they changed to 6.99 from this date to that date. Let's go ahead and scroll down just a little bit more, and I'm done looking at my Temporal Tables, so go ahead and delete this database. Temporal Tables open up a world of possibilities when it comes to tracking the changes of records over time. Being able to check back on values as they used to be, and not just as they are right now, is a real game-changer for SQL Server. I'll add just one last parting bit of advice. Temporal Tables are not to be confused with temporary tables, even though their names are quite similar. Temporal Tables track changes over time, while temporary tables simply cease to exist over time.

Contents