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

Published on: 2017-07-19

Bordeaux, The Grand Theatre” by Stefano Montagner is licensed under CC BY-NC-ND 2.0

Have you ever needed to look at what data in a table used to look like?

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, SQL Server 2016 introduces a new feature to make our point-in-time analysis queries easy to write: temporal tables.

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:

  1. The temporal table — this is the table that contains the current values of your data.
  2. 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 temporal table is different from this because:

  1. You don’t need to write any triggers/stored procedures! All of the history tracking is done automatically by SQL Server.
  2. 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:

The key things to note with our new table above are that

  1. it contains a PRIMARY KEY.
  2. it contains two datetime2 fields, marked with GENERATED ALWAYS AS ROW START/END.
  3. It contains the PERIOD FOR SYSTEM_TIME statement.
  4. It contains the SYSTEM_VERSIONING = ON property with the (optional) historical table name (dbo.CarInventoryHistory).

If we query our newly created tables, you’ll notice our column layouts are identical:

Let’s fill it with the choice car of car rental agencies all across the U.S. — the Chevy Malibu:

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 dbo.CarInventory and the bottom result is our historical table 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!

Now we see our temporal table at work: we updated the rows in dbo.CarInventory and our historical table was automatically updated with our original values as well as timestamps for how long those rows existed in our table.

After a while, our customers return their rental cars:

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 UPDATE statement. It’s automatically keeping track of all of this history for us!

Continuing on, business is going well at the car rental agency. We get another customer to rent our silver Malibu:

Unfortunately, our second customer gets into a crash and destroys our car:

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 SYSTEM_TIME AS OFto show us what our table looked like at that point in the past:

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:

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 FOR SYSTEM_TIME AS OF. Long gone are the days of needing complicated queries to rebuild data for a certain point in time.

 

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

Here’s a Quick Way To Generate a Running Total in SQL Server

Published on: 2017-07-18

SQL in 60 Seconds #2

Historically it’s been difficult to accomplish certain tasks in SQL Server.

Probably the most annoying problem I had to do regularly before SQL Server 2012 was to generate a running total. How can a running total be so easy to do in Excel, but difficult to do in SQL?

SUM(), click, drag, done. Excel, you will always have a place in my heart.

Before SQL Server 2012, the solution to generating a running total involved cursors, CTEs, nested subqueries, or cross applies. This StackOverflow thread has a variety of solutions if you need to solve this problem in an older version of SQL Server.

However, SQL Server 2012’s introduction of window functions makes creating a running total incredibly easy.

First, some test data:

Next, we write our query using the following window function OVER() syntax:

The syntax for our OVER() clause is simple:

  • SUM(Price) specifies which column we are creating the running total on
  • PARTITION BY specifies around what group of data we want to create our “window” — each new window will reset the running total
  • ORDER BY specifies in what order the rows should be sorted before being summed

The results? An easy to write running total:

 

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

My Most Embarrassing SQL Moment

Published on: 2017-07-11

T-SQL Tuesday #92: Lessons Learned the Hard Way

This post is a response to this month’s T-SQL Tuesday prompt. T-SQL Tuesday was created by Adam Machanic and is a way for SQL users to share ideas about interesting topics. This month’s topic is Lessons Learned the Hard Way.


“Is this your query that’s killing the server?”

It was my first week on the job and I was learning to query one of our major databases.

Up until that point, my SQL experience was limited to working on a *tiny* e-commerce database. Query performance was never something I had to deal with because any query I wrote, no matter how poorly written, would always execute quickly.

This new database I was working on though had tables with a billion+ rows. I should have been more conscious about how I was writing my joins and filtering my records, but I wasn’t. I wrote my query and executed it in SQL Server Management Studio.

About 20 minutes into my query’s execution, I received an email from my new DBA, and it looked something like this:

Uhh, there might be a problem here

“Is this your query that’s killing the server?”

Oops.

I don’t think my mouse ever moved to the stop execution button as quickly as it did that moment.

I was incredibly embarrassed to have brought our production server to a crawl. I was also incredibly embarrassed to have had my first interaction with my new DBA be about a query that created major problems for him.

Although there were no long-term damages from my server-crushing query, it was a scenario that I definitely didn’t want to relive again in the future.

Next time: don’t do that again

Obviously, this was an experience where I learned that maybe I shouldn’t write queries against unfamiliar data in production.

  • I should have been practicing on a dev database.
  • I should have looked at table meta data and made sure I understood relationships between tables better.
  • I should have done some more preliminary querying with more restrictive filters to be able to catch performance problems earlier on with smaller result sets.
  • I should have examined what indexes were available and made sure I was attempting to use them.
  • I should have used a (NOLOCK) if I absolutely had to test on the production data so that at the very least I wouldn’t prevent the high transaction ETLs from modifying data in that database.

All of those “should haves” quickly became my checklist for what to do before running any query in an unfamiliar environment.

I’ve still written plenty of ugly and inefficient queries since then, however none of them ever caused me to bring the SQL server to a halt like I did in my first week. That was one lesson that I learned the hard way.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!