Temporal features and temporal tables clearly explained and with examples! (Special: WHO'S WHO in the SQL world pt/2.. Mr. Krishna Kulkarni)

Hi guys,

Welcome to this new post! This time we will talk about temporal features and temporal table (attention please... not temporary tables).

We never talked about this topic in this blog but today is the right day to do it!

Why is this an important topic?

Well, how many times you had to  track changes in data contained into a table? I am sure, so many times!

Probably you solved this problem of tracking changes to your data (and so to know what happened to your data in the past) using triggers. A trigger is fired every time the value of a field in a table change. This trigger will add a row into a log table.

It is necessary to know that this operation is natively supported by many database systems and also our Microsoft SQL Server has been supporting it for several years.

Enjoy the reading!

 

SQL:2011 and the Temporal Features. A Bit of Theory.

We start talking about temporal tables by saying that they are supported by different database engines because they have been defined as SQL language standard.

SQL:2011 (the seventh revision of the ISO and ANSI standard for the SQL database query language) was published in December of 2011, replacing the previous SQL:2008 release.

Well, one of the most important new functionality introduced as part of SQL:2011 standard is just the ability to create and manipulate temporal tables.

We are talking of tables whose rows are associated with temporal periods and indeed, time period is a keyword of this post!

Yeah, but what is time period?

Period

Well, a time period is a mathematical concept: an interval on the timeline demarcated by a start  time and an end time.

How to manage a time period in a rdbms? You could manage a time period by adding a dedicated data type. Very cool but this is a difficult road!

The SQL:2011 standard has chosen to manage time periods by adding the period definition as metadata.

A period definition is a named table component identifying a pair of columns that capture the period start and the period end.

The syntax of the CREATE TABLE and ALTER TABLE statements has been enhanced to support the creation and the destroying of period definition.

The period start and the period end columns are so conventional columns.

Now that we know the concept of period and how it is supported in the SQL:2011 standard, we also have to learn that there are two different dimensions of time for temporal data support. 

This two dimensions are called valid time and transaction time.

Dimensions of time for temporal data support: Valid time and transaction time

Valid time is the time period during which a row in the table is regarded as correctly reflecting realty by the user of the database.

Transaction time is the time period during which a row is committed  to the database.

Valid time may differ from its valid time: for example in a database we have a table that contain a Price list. Informations about a Price of an item may get inserted (this is the transaction time) before that price comes into effect (this is the valid time).

In SQL:2011 transaction time support is provided by system-versioned  tables with in turn contain the system-time period.

Valid time support is provided by tables containing an application-time period.

Let's see now first the application-time period tables then the system-versioned tables.

Application-time period tables

This tables are intended to support application  that must capturing  time periods during which the data is believed to be valid in the real world.

A typical example could be manage a price list. 

A price list usually have a date of validity.

I can enter a new price that will be valid only from a date in the future.

In this case is the user that set the start and the end times of the validity period.

The syntax specified in the SQL:2011 standard is the following:

CREATE TABLE Emp(
ENo INTEGER,
EStart DATE,
EEnd DATE,
EDept INTEGER,
PERIOD FOR My_Period (EStart, EEnd)
)

This is an application-time period table because the period definition has an user-defined name (My_Period)

To be clear INSERT, UPDATE and DELETE statements works as in a normal table but the syntax in the SQL:2011standard is expanded in the UPDATE and DELETE statements to allow you to manage changes that are effective only within a specified period.

You can use the "FOR PORTION OF" with "FROM DATE" and "TO DATE" extensions:

UPDATE Emp
FOR PORTION OF EPeriod
FROM DATE '2011-02-03'
TO DATE '2011-09-10'
SET EDept = 4
WHERE ENo = 22217

The main question is what can we do with this type of table?

Some interesting things...

 

Example

Suppose you have an EMP table that contains data about the department (EDept) where an employee works.

The Employee 22217 works in the Department 3 from the first day of the year 2010 until the date  2011-11-12

This employee at some time has to move to the new department 4 but will only do so from 2011-02-03 to 2011-09-11. Then he will return to Ward 3.

The UPDATE statement is the one previously seen:

UPDATE Emp
FOR PORTION OF EPeriod
FROM DATE '2011-02-03'
TO DATE '2011-09-10'
SET EDept = 4
WHERE ENo = 22217

The result is the following..

Wow! very nice! don't you think?

We have the complete history of our data showed in a very clear way!!!

 

And if I had cancelled the same period of time? what would have happened?

Well..

 

I start again from the same data...

Then i execute the following DELETE statement:

DELETE Emp
FOR PORTION OF EPeriod
FROM DATE '2011-02-03'
TO DATE '2011-09-10'
WHERE ENo = 22217

This is what i get:

Yes, I get two rows. A row has been deleted while two rows are added.

 

 

Very nice but there is a problem SQL Server DOES NOT SUPPORT Application-Time period table! 

What a pity!

 

Let's see now the next type of temporal table, the system-versioned tables.

System-versioned tables

This type of tables are intended for meeting the requirement of application that need to mantain an accurate history of data changes for whatever reason.

For example in a banking application it is very mandatory to keep track of all the account statuses of its customers.

This type of table is supported by our Microsoft SQL Server. Yay!!

This means that we can use this feature and avoid having to make it ourselves through additional SQL instructions then with an overhead!

With the system-versioned tables we have the following 3 key concept:

• Every UPDATE or DELETE on a row will preserve the old state of the row.
• The system will mantains the start and the end of the period.
• Users cannot modify the contents of historical rows.

 

Let's see now how to use the System-versioned tables!

Just remember that any table that contains a period definition with the name SYSTEM_TIME and includes the keywords WITH SYSTEM VERSIONING is a system-versioned table.

The following is the specific syntax used in the T-SQL language used by Microsoft SQL Server so you can try it now!

CREATE TABLE dbo.PriceList
(
    [ID] int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
  , [IdList] INT not null
  , [IdProduct] INT
  , [Price] Float
   ,[ValidFrom] Datetime2 GENERATED ALWAYS AS ROW START
  , [ValidTo] Datetime2 GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PriceList_History));

This table represents a data structure for contains data related to a price list.

Each product (idproduct) has a price for the List (idlist)

....it is just a simple example.. rememeber...

 

Once you create this table you will see it in the object explorer window of the SSMS with a new icon:

 

 

So it’s true! "version-system tables" ...really ....exist !!!

 

 

Le'ts now see how to work with this type of tables

We must remember that we cannot assign the values of the beginning and end of the period because they are managed by the system, and so, when i execute an INSERT statement, i cannot specify the values for the ValidFrom and ValidTo fields.

 

First we insert a row inside the table:

INSERT INTO PriceList (IdList, IdProduct, Price)
  VALUES (1,1,9.88)

The content of the table will be the following:

 

Now let's see what happens when we UPDATE or DELETE a row.

 

If now I UPDATE a row to change the price with the following command:

UPDATE PRICELIST
  SET PRICE = 10.01
WHERE 
  ID  = 1

When I show the content of the table PRICELIST with the following command:

 
SELECT * FROM PRICELIST

I get this data:

 

But if I use the FOR SYSTEM_TIME extension..

 
SELECT * FROM PRICELIST
  FOR SYSTEM_TIME
    BETWEEN '2021-01-01 00:00:00.0000000' AND '9999-12-31 23:59:59.9999999'

 

I get also the historical data:

 

WOW!

All this works is done by SQL automatically and without this feature to obtain the same result
I should have used at least one trigger!

Suppose now that our product no longer has to have any price and then I have to cancel it from the PRICELIST table.

 

I'll  use the following  command:

DELETE FROM PRICELIST
WHERE 
  ID  = 1

What do I get?

With the command

 
SELECT * FROM PRICELIST

I get correctly an empty table:

While if I use the following command:

 
SELECT * FROM PRICELIST
  FOR SYSTEM_TIME
    BETWEEN '2021-01-01 00:00:00.0000000' AND '9999-12-31 23:59:59.9999999'
 

I get:

We have two rows, both have a no longer valid end date.

 

To summarize:  

• UPDATE and DELETE operation, as said previously, operate only on current system rows. 
• Each UPDATE and DELETE led to the automatic insertion into the table of an historical system row.
  

Maybe in a future post we will make a comparison between this solution with the one that uses triggers to populate a table with historical data.. what will be the fastest? stay tuned...

For today we will not go into further details related to time tables ...such as bitemporal tables also because they are not supported by Microsoft SQL Server.

In the next paragraph, for the WHO’S IS WHO series, we will briefly mention who created this theory and formalized it as a standard.

WHO'S IS WHO in the SQL World ...pt.2

All the theory that today I have told to you is born from the study of two IBM researchers Krishna Kulkarni and Jan-Eike Michels and it is included in the pubblication "Temporal features in SQL:2011" and published on ACM SIGMOD Record, Vol. 41, No.3 (1 set 2012).

Krishna Kulkarni is a Indian born Senior Software Engineer that worked for IBM us from 1997 until 2015. At IBM we was the primary architect and driver of the SQL standard activity.

 

He represented IBM at the ANSI SQL committee as the Primary Member.

 

He is actually living in San Jose (CA) as a Database Language architect and provide consulting on SQL and related database language standards.

 

 

 

That's all for today!

I hope you liked this post and I want to give you my best wishes for a peaceful Christmas!

~Luke

 

Previous post: Working with DATES in a SARGABLE way ...and gofast! ...All clearly explained!