Learning Corner #3 – SQL Server 2016 – Temporal tables

Posted on by SQL by the book

SQL Server 2016 introduces support for an interesting new feature, called Temporal Tables. As you may see on other sites as well, this comes with the following warning: Don’t confuse them with Temporary Tables!

I will try to explain in this post (and maybe in some others, following this one) the main functionality of the temporal tables and why I chose to try out this feature of SQL Server 2016 before other ones.

After reading this post, it should be clear what these tables are, how can we use them in our current solutions, how they work and what are the limitations of their usage (because everything has some limitations)

The main parts of my post are:

  • Definition
  • Reasons for implementation
  • How does it work? Create a new temporal table
  • Converting a non-temporal table to be a temporal table
  • Changing the schema of a temporal table
  • System consistency checks
  • Query temporal data
  • Metadata views and functions
  • Security
  • Constraints
  • Partitioning

In this post, I will cover only the first 4 items in the list below.

So here goes 🙂

Definition

According to MSDN, the definition for a temporal table is the following:

“A temporal table is a table for which a PERIOD definition exists and which contains system columns with a datatype of datetime2 into which the period of validity is recorded by the system, and which has an associated history table into which the system records all prior versions of each record with their period of validity. With a temporal table, the value of each record at any point in time can be determined, rather than just the current value of each record. A temporal table is also referred to as a system-versioned table.”

(Source: https://msdn.microsoft.com/en-us/library/dn935015.aspx)

…Anywaaay, after reading for the first time this long definition, I figure out this has something to do with keeping history for some tables in SQL Server. This is a functionality implemented by hand in most software products, but mostly in the data warehouses.

For example, in the last BI solution I have been developing, we had (and still have) the following structure:

  • A data warehouse table (can be a dimension or a fact table), with a timestamp column (we would have used a datetime2 or a different type of column for keeping track of changes, but the source systems did not allow that, so we used what we could). This type of table always has the latest changes from the source systems.
  • A history table per data warehouse table, that contains a row for all the changes that happened in a column of a row in the DWH table. This is used mostly for data warehouse reporting purposes, so that we don’t have inconsistency in the reports.
  • The history tables are stored on disk, created at installation time and they need to be maintained.

01. HistoryTable

From what I understood by reading the MSDN documentation, the temporal tables offer a possibility to replace completely our in-house functionality with a standard solution provided by SQL Server.

Reasons for implementation

The reasons for this functionality, as mentioned by Microsoft, are mostly centered on the fact that data is very dynamic and we need to keep track of what is changing inside our databases in order to make decisions that are 100% backed-up by data.

In conclusion, we need temporal tables for the same reasons we need history in a data warehouse.

The most important reasons for starting to use temporal tables are summed up in the image below:

WhyTemporal 3

How does it work?

(DEMO: Creating a new temporal table)

For this demo, I will use the database named SQL2016_TestDB, which I created using the following command:

CREATE DATABASE [SQL2016_TestDB]

I created a dimension table, dim_Product, with a few columns:

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'dim_Product') DROP TABLE [dim_Product] GO CREATE TABLE [dim_Product] ( [ID] INT IDENTITY(1,1) PRIMARY KEY, [_DwhKey] [NVARCHAR](250) NULL, [Name] [NVARCHAR](50) NULL, [Description] [NVARCHAR](50) NULL, [UM] [NVARCHAR](50) NULL, [Price] [DECIMAL](18,4) NULL ) ON [PRIMARY] GO

Then, I alter my table by adding the necessary two PERIOD columns:

ALTER TABLE [dbo].[dim_Product]
ADD PERIOD FOR SYSTEM_TIME (AvailableFrom, AvailableTo) ,
AvailableFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL 
     CONSTRAINT DF_AvailableFrom DEFAULT GETUTCDATE(),
AvailableTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL 
     CONSTRAINT DF_AvailableTo DEFAULT 
          CONVERT(DATETIME2, '9999-12-31');

This syntax is new and specific for these kinds of actions. As you can see, I am adding a PERIOD member in the table, for the two new columns. Then, I define each column, following these considerations:

  • The AvailableFrom column must have the default system date (GETDATE(), GETUTCDATE())
  • The AvailableTo column should be filled in with a default value highly in the future (exp: 9999-12-31)

The table is filled in with values for my product. If you want to fill your table with some values fast, just use the script below:

INSERT INTO [dbo].[dim_Product] ([_DwhKey], [Name], [Description], [UM], [Price])
VALUES
('1|P001', 'Teddy Bear toy', 'Very cute, brown, Teddy bear', 'BUC', 20.99),
('1|P002', 'Barbie doll', 'Dancing queen Barbie', 'BUC', 52),
('1|P003', 'Sun glasses', 'Polarized sun glasses', 'BUC', 12.99),
('2|P001', 'Strawberries', 'Fresh, from the country', 'KG', 1.99),
('2|P002', 'Milk', 'From the Milka cow', 'L', 3.99)

Until now, I created a normal table, without anything special about it.

I will also create the history table by myself, because otherwise SQL Server will give it a very ugly name.

CREATE TABLE [dim_Product_history]
(
[ID] INT NOT NULL,
[_DwhKey] [NVARCHAR](250) NULL,
[Name] [NVARCHAR](50) NULL,
[Description] [NVARCHAR](50) NULL,
[UM] [NVARCHAR](50) NULL,
[Price] [DECIMAL](18,4) NULL
) ON [PRIMARY]

OBS: One consideration to keep in mind when using this functionality is that

  • the principal table must have a primary key
  • the history table must NOT have a primary key
  • the history table must NOT have IDENTITY columns
  • the history table must have the same nullability for all columns as the principal table

I am adding the period columns to the history table as well, but without the constraints:

ALTER TABLE [dbo].[dim_Product_history]
ADD
AvailableFrom DATETIME2 NOT NULL,
AvailableTo DATETIME2 NOT NULL

And now comes the most important part: adding the system versioning to the Product table. The syntax basically says: I and to add versioning to this and I will keep the history in a mentioned table. If the history table is not mentioned, a new table will be created.

ALTER TABLE [dim_Product]
SET
(
SYSTEM_VERSIONING = ON  
     (
      HISTORY_TABLE = [dbo].[dim_Product_history], 
      DATA_CONSISTENCY_CHECK = ON
     )
);

Now that everything is ready, let’s see how this is working:

This is the initial status of the 2 tables:

SELECT * FROM [dbo].[dim_Product]

03. Product_InitialStatus

SELECT * FROM [dbo].[dim_Product_history]

04. Product_history_InitialStatus

Now let’s make some changes in the products:

UPDATE [dbo].[dim_Product]
SET Price = 21.99
WHERE _DwhKey = '1|P001'

05. Product_Update1

06. Product_history_Update1

UPDATE [dbo].[dim_Product]
SET Price = 100
WHERE _DwhKey = '1|P001'


UPDATE [dbo].[dim_Product]
SET Price = 22.99
WHERE _DwhKey = '1|P001'

UPDATE [dbo].[dim_Product]
SET UM = 'Something else'
WHERE _DwhKey = '1|P003'

07. Product_history_Update2

DELETE FROM  [dbo].[dim_Product]
WHERE _DwhKey = '1|P001'
DELETE FROM  [dbo].[dim_Product]
WHERE _DwhKey = '1|P002'

After all these changes, the Products table looks like this:

08. Product_Update2

… and the history table looks like this:

09. Product_history_Update3