Claus on Code

SQL server 2016 has been available for preview for some time now. One of the more exiting features I am looking forward to is system versioned tables.

As a DWH developer, one of the things, you spent a lot of time with, is developing dimension tables with columns that stores history. The history is typically in the form of Type1 or Type2, where Type1 is no history, and Type2 is where you store the history, by adding another row, when a value changes for a given natural key in the source system.
Type2 history is normally developed by using

• A DWH framework
• 3rd Party component
• Developed specific for the dimension that needs the Type2 history

In version 2016, SQL server can help you with this, using system-versioned tables.

Right click tables->new-> And then you see a new option called “System-versioned Table”

There is no designer for it yet, so we will do it in SQL, which look like this.

CREATE TABLE dbo.Product
(

SK_Product int IDENTITY(1,1) Not Null,
BK_productid INT NOT NULL
CONSTRAINT PK_product PRIMARY KEY NONCLUSTERED,
ProductName NVARCHAR(50) NOT NULL,
ProductCategory NVARCHAR(50) NOT NULL,

sysstart DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL,
sysend DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (sysstart, sysend)
)
WITH ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo. ProductHistory ) );

Where

• SK_Product is the surrogate key
• BK_productid is the natural key
• ProductName and ProductCategory are collumns we want to track history on(Well, preferably only ProductCategory, but this is not on column level yet)
• Sysstart is the valid from
• Sysend is the valid to
• And PERIOD indicates which collumns to be used for valid from and to.

Required columns are of course a primary key, Sysstart, Sysend and PERIOD.
Below a screenshot of what it looks like in management studio.

As you can see, the producthistory table is placed under the product table, to show the relationship.
Let us try to insert some values:

INSERT INTO [dbo].[Product]
([BK_productid]
,[ProductName]
,[ProductCategory])
VALUES
(1,'Product1','Category1')

So, the insert, only insert a row in the current table, and no rows in the history table.
Let us try with an update:

UPDATE Product
SET ProductCategory = 'CategoryUpdated'
WHERE BK_productid=1

So, the update, updates the current table, and insert a row in the history table, hence, we will always have the current value in the “current table”, and old values in the history table.

Conclusion

When would you use this feature?
It could be helpful in an OLTP system, where they know they will need the history for reporting in the future, but does not have a DWH yet, to store the history.

There will be an overhead in performance and storage.

Would we use it in a DWH, for a dimension with type2 history?

Properly not. This is one specific implementation of it, and we cant change anything to the implementation, so we would have to use it in combination with our specific needs, and then it would be easier not to use it at all.

This could be useful in an archive layer in a DWH, where you want to store all changes of the extracted values over time.

This blog post is based upon a CTP version of SQL server, so much could still change.
Read more about it here: https://msdn.microsoft.com/en-us/library/dn935015.aspx