Temporal Tables in SQL Server 2016

   

     Auditing the data changes in your database is an important job. In my career, I have created good amount of Audit tables to track who did what/when. SQL Server 2016 makes auditing easier for us. Unless you need a real custom Auditing, you may not need to create Audit tables anymore if you have SQL Server 2016.

    If the table you are creating needs auditing, you can declare it as a Temporal Table. From that point, SQL Server will handle tracking insert/update/delete transactions. By default SQL Server creates the same table with a different name as a history table. Your real table keeps the current data, and the history table created by SQL Server keeps all the historical data of your real table.

   First you need to be sure that there is a primary key in your table. Also you need to define one PERIOD FOR SYSTEM_TIME with two datetime2 columns. These two datetime2 columns will be used to track the updated values date range. If you are creating the history table manually, you need to be sure that schema of your history table matches to your source table.

    There are three different ways to create Temporal Tables, Easiest way is the anonymous history table way. Basically you don't customize the history table and SQL Server creates the history table for you.

     You need to declare two datetime2 columns for Temporal table to work. This is the most basic way to create Temporal table. WITH (SYSTEM_VERSIONING = ON) tells database to create Temporal Table for the table we are creating. It requires the two datetime2 columns and the Period For System_Time line which defines which columns to use for versioning.

I am going to insert new rows to this table, Let's see what it's going to happen.

      That looks good to me, Let's try to Update one of these rows to see how SQL Server is going to handle it. I am going to discount my 60W Light Bulb. I want to sell it for $5 rather than $6

     Table is updated, but how do I know auditing is working? We need to query our auditing table to figure that out. Since we used the basic way to create Temporal Table, we did not define a name for it. Default name is in MSSQL_TemporalHistoryFor__[suffix] format. Let's look at the Object explorer to find the history table.

Let's see what is in history table.

    Products table has the latest value for my 60W Light bulb which is discounted $5 value, and the history table has the earlier values of it. Also it tells me the date range for the earlier values. I am not really fan of the name of this table, so I want to declare its name when I create the source table, You can use the HISTORY_TABLE option to declare history table name.

     If this table already exists in your database, SQL Server will validate its schema first. It will fail if the schema doesn't match to your source table. Also if you are creating your history table manually, you may want to create indexes on it for a better performance too.

Everything is great so far but I don't really want to see the new datetime2 columns in my source table. There is a quick trick for that if you like to hide them in your source table (Products)

    DateTime2 values are still there, but they are hidden now.

    Temporal tables come with special SELECT .. FROM Clause to help you to join your source and history tables. FOR SYSTEM_TIME clause has five temporal specific sub clauses to query your source and the history tables. In this way you can query the historical values of any value. To demo this I have updated my product couple of times,

As you can see without manually joining my source and history tables, I got the current and history values of my product here.

    As a web developer it is going to make my life easier for sure. You can alter your tables to support Temporal tables, just be careful because depending on the size of your tables, it might take time to create Temporal tables. Also don't forget to create indexes on your temporal tables.