Press "Enter" to skip to content

Category: Temporal Tables

Temporal Database Theory

Kennie Pontoppidan reads and reviews a book on temporal database theory:

I have chosen to blog about Richard T. Snodgrass’ book “Developing time-oriented database applications in SQL.” I heard about this book last year around this time, when I started to investigate the new temporal feature “System versioned tables” in SQL Server 2016. I believe it was my old colleague Peter Gram from Miracle who pointed out the book to me, and usually when Peter recommends a book, I buy it and (eventually) read it. It was also about time (no pun intended), since I’m giving a talk on “All things time-related” for two SQL Saturdays during the next few months, and I needed to spice up the presentation with some new material.

In this blog post, I will quickly scratch down a few of the takeaways, I have taken from the book already.

Sounds like an interesting read.

Comments closed

Always Encrypted And Temporal Tables

Raul Gonzalez wants to combine Always Encrypted with temporal tables in SQL Server 2016:

Again the wall. There is no way you can choose a temporal table and apply encryption to a column or columns using the wizard.

I tried then using the powershell (after manually creating the keys) as this is true the only way to encrypt existing columns, just in case.

Raul doesn’t stop there, though, and he does figure out a workaround.

Comments closed

Temporal Tables Via SMO

Cody Konior shows how to create temporal tables in SQL Server 2016 using Powershell and Server Management Objects:

Why would you want to do this? In my case I have a PowerShell function to accept an input object, convert it to a DataSet (a .NET representation of tables and the links between them), and bulk copy the data into SQL Server. However because the input objects can vary slightly with additional tables or additional columns I use the flexibility of SMO to create the tables and columns on the fly.

There’s a lot of power in programmatically defining objects.  I’m not sure that’s a good default scenario, but there are times in which it can come in handy.

Comments closed

When To Use Temporal Tables

Randolph West wraps up his temporal tables series by asking when you should use them:

Tracking changes to your data is a big deal, and in databases with heavy churn, you can end up needing a massive amount of space to handle your history, especially if you need to retain seven years of data, like some of our customers.

If data storage is a concern, I’m going to recommend SQL Server Enterprise Edition. The Books Online documentation specifically states that the history tables for Temporal Tables are implemented with Page Compression by default, which is an Enterprise Edition feature.

My quick thought is, use them when you want a type 2 dimension without putting in the effort to create a type 2 dimension—in other words, non-warehouse systems in which you need long-term data changes.

Comments closed

More On Temporal Tables

Newly-minted Canadian citizen Randolph West’s latest article is on modifying temporal tables:

Last week I demonstrated how temporal tables in SQL Server 2016 work. If you have implemented a history table—populating it with triggers or stored procedures—it works the same way.

This week, we are going to look at how to modify a temporal table that already has data in the history table.

If you’re using temporal tables for auditing, it’s important to know that yes, data can be modified.

Reza Rad also has a recent blog post on temporal tables:

Temporal tables are new type of database tables introduced in SQL Server 2016, these tables are system-versioned and keep history of changes (insert, delete, update) of everything happened on data rows. Retrieving change log from these tables are easy. These tables can simply tell you what was the data at specific point of the time in the table. These tables works with datetime2 columns to keep FROM DATE and TO DATE information of each change. This means these tables can be used for implementing changes in dimensions, yes you know what it called; Slowly Changing Dimension!

Slowly-changing dimensions in an OLTP scenario was the first use I could think of for temporal tables, so I’m glad Reza put this article together.

Comments closed

More On Temporal Tables

Randolph West has another nice post on temporal tables, in which he lets the cat out of the bag:

Look at the log records. They are identical to before. In fact, because a trigger is called in the same implicit transaction as the UPDATE statement, we didn’t even need to wrap the INSERT statement in a transaction.

What have we learned?

Temporal tables are doing exactly what an update or delete trigger (or stored procedure) would do, based on what we saw in the log records.

This is an in-depth look at what, exactly, is happening when temporal tables get updated.

Comments closed