Sql-server – Using SQL Server’s 2016 Temporal Tables to Record Prior History

sql serversql-server-2016temporal-tables

I've been reading with interest about SQL Server 2016's Temporal Table capability.

It almost seems like the answer to a problem I have (and I'm sure many have had), except for one thing:

StartTime and EndTime durations are immutable.

Which is great for transactions going forward…but what if you want to model stuff that changed in the past?

Is it possible to model things like "Organization A was named X between 1970 and 1980, Y between 1980 and 1990, and Z since 1990" using temporal tables? Or are we stuck with traditional methods for this stuff?

It looks to me like temporal tables are only good with changes that happen after the temporal table is created, but I'd like someone to confirm or deny that. (Bonus points if my assumption is correct and you can point me to something that describes the ideal way to implement tables and queries to handle historical queries.)

Best Answer

Currently temporal supports system time, which means the date/time values placed in those start/end time columns are populated by the system.

In a future version, temporal will also support application time, which will provide ways to have the application dictate the time and, I expect, to update existing data with real start/end times.

The only way to override this really would be to archive data from the history table into your own table (which you may end up doing over time to keep the history table from growing forever anyway), change the times there, and use that table as part of your queries.

Related Solutions

Sql-server – Query strategies using SQL Server 2016 system-versioned temporal tables for Slowly-Changing Dimensions

I think, in your case a derived table is necessary to isolate querying number of mutations of postcodes per customer:

SELECT c.postalcode 
, sum(s.SaleAmount) SaleAmount
, count(postcode_mutations.customerid) as CntCustomerChangedPostCode   
FROM dbo.Sale s
JOIN dbo.Customer c on s.customerid = c.customerid

LEFT JOIN (
SELECT 
    CustomerID
FROM [dbo].[Customer]
FOR SYSTEM_TIME FROM '20140101' TO '20150101'
GROUP BY CustomerID
HAVING COUNT(DISTINCT PostalCode) > 1
) postcode_mutations on s.customerid = postcode_mutations.customerid

WHERE s.SaleDateTime >= '2014-1-1' AND s.SaleDateTime < '2015-1-1'
GROUP BY c.PostalCode

upd: Since the query is supposed to serve DWH/Analytics scenarios, the columnstore indexing is an option to check. I also made some benchmarks previously for a 10 million rows table.

SQL Server 2016 – Will Temporal Tables Be Included in Standard Edition?

_{Community Wiki answer generated from question comments. Feel free to edit it to improve it.}

Anthony Faull: The Temporal Tables feature is available in all editions of SQL Server 2016 including Standard Edition, Web Edition and Express Edition. It is not limited to Enterprise Edition.

Best Answer

Related Solutions

Sql-server – Query strategies using SQL Server 2016 system-versioned temporal tables for Slowly-Changing Dimensions

SQL Server 2016 – Will Temporal Tables Be Included in Standard Edition?

Related Question