THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is

SQL Server 2016 Temporal Tables and Type II Data Warehouse Dimensions

This blog has moved! You can find this content at the following new location:

Published Friday, September 18, 2015 7:21 PM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Greg Low said:

Hi Andy, I can't see it working for type 2 SCD's without app-temporal time rather than system temporal.



September 20, 2015 7:36 AM

andyleonard said:

Hi Greg,

  I usually (not always) push application time zone management into the reporting solution. In the US, this solves issues with time zones and those pesky switches between Daylight Savings Time.


September 20, 2015 7:41 AM

Steve Fibich said:

Hi Andy, this looks really nice and handles some temporal issues but we/I/you/the DW community would still need a way at looking at attribution based on the source systems effective dating of attribution.  Its a good start on MS part but they need a way to have a user defined effective dating column.  So you could write a statement that says something like....

Select * from TestTable System_Time as of '9999-12-31' and SOURCE_EFFECTIVE_TIME as of '2015-01-01'.  I don't have a really good idea of how to load or how to represent the SOURCE_EFFECTIVE_TIME.

September 22, 2015 7:21 AM

W. Kevin Hazzard said:

Steve nailed it. This is a good concept but there are some bits of transactional metadata that should be transparent with a solution like this. Azure SQL Database with Mobile Services makes this totally transparent. I really, really wish Microsoft had taken that approach with system-versioned tables in the on-prem DB.

September 22, 2015 3:42 PM

shaun said:

You've showed the historical rows getting end dated after the update. Do the new current rows (type 2 inserts) get new surrogate keys? Since that would have to be inserted into the fact to link in the correct version of the row assuming you're tracking the change in relationship to facts.

September 25, 2015 9:04 AM

andyleonard said:

Greg, Steve, Kevin, and Shaun,

  Those are excellent thoughts, and solid limitations to using temporal tables to manage Type II data warehouse dimensions.


September 25, 2015 9:16 AM

Greg Low said:

There is a standard for how this should be done. That's the distinction between sys-temporal (what we got) and app-temporal (what's needed most of the time), and bi-temporal (that provides both).

September 21, 2016 12:34 AM

Leave a Comment


This Blog



My Latest Book:

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement