Add Application-period temporal tables and Bitemporal tables to SQL Server 2016
SQL Server 2016 has temporal tables however these are only for system period temporal tables.
Please add Application-period temporal tables and Bitemporal tables similar to the existing feature in DB2.
Upvotes: 29<=-=Jul 27 2016 5:17PM=-=>
This feature vastly simplifies development of certain types of applications. Would be interesting at least know if microsoft has some intention to implement it.<=-=Jul 27 2016 5:19PM=-=>
A fully compliant implementation to ansi sql 2011 would nice too.<=-=Jul 28 2016 1:01AM=-=>
I would also like to see bitemporal support added, it would be most useful!<=-=Mar 7 2017 11:54AM=-=>
This feature would really help people trying to migrate their DBs from DB2 or Oracle to SQL Server that much easier.
Graham McGibbon commented
Any update on this Microsoft? I see the original request goes back 6 years!
This feature, incorporating temporal real-world dates, would make be great, and adding the ability to stage future dates (as somebody mentioned below) would just make incredibly powerful.
I can think of so many scenarios that past temporal real-world dates would accomplish in my space, clinical trials. Adding future temporal real-world dates would help to fix many headaches in clinical trials with respect to protocol amendments, that are often known in advance but difficult to implement, and also validate and track.
Ajay Narvekar commented
Enabling application period temporal tables will ease the efforts to handle type 2 SCDs in the ETL.
Jim Rosengarth commented
This same logic applies to tax tables. There is a need to post temporal data in advance that will take effect on a specific date/time. Another use case I have run into is currency exchange rates. These get updated daily by a running job, but take effect at exactly midnight on the day the rate changed. Financial reports then need to reflect the correct values based on the transaction time.
Actual practical scenario can not use temporal feature as it now,
below are some scenario which highlight the limitation of temporal in MSSQL ,
1- Assume you need to load the DWH from a source system using a Patch mode , Where Every Day DWH team will receive a CSV file contains all the changes occurred at a certain date.
So It will happened that the file itself will be rec. at 15 Jan 2019 3:00 AM as an example from the IT team but will containing the updates occurred at 14 Jan 2019. DWH team should not relay on the time at which the file was rec. but on the time located in the CSV file.
2- If DWH found that a file contains incorrect data has been loaded one week ago , and a recovery actions need to be taken, the situation now is I need to deleted the records from the DWH and Reload a new file using a back Date
deleting from the table mean delete the Historical record them-self, and Restore the table as if One week ago , then start load the date for 1 week .
3- In many cases once after building DWH , I may found my self need to load a Historical records for some tables, The historical can be found at archive file at IT department ,
4- In some cases a Copy of the DWH database may be needed as a disaster recovery for example , Now I need copy the rows from MSSQL 1 to MSSQL 2 , and Keep the same start date and End date.
I see temporal feature in MSSQL is missing important point which can be found in Teradata,
I want to ask for this feature in MSSQL. but I donot know where to place this request.
Temporal feature in Teradata can be used to build a Slow change dimenssion table , Which is a very common tasks in DWH
By allowing this below point ( It is a limitation in MSSQL till now )
1- Both history records and current record are stored in one table, which is good for any DWH solution , ( MSSQL use two tables one for Current version of the row and one history )
2- It allow me to run the update statement on and specify the effect date , ex I can run an update statement on a table as specify that I want this update to be runas of 3 days ago , the start date of the records will be 3 days ago . Currently MSSQL use system cal. and no way to load history record by control the start date colum.
I had rolled my own tables before and was happy to hear that MS made temporal tables a built in feature.
I don't know why they only targeted live transaction databases and left people working with non-live data out in the cold.
I really hope this one gets implemented.
While I'm very excited to see system-supported temporal tables coming to SQL Server 2016, I was slightly disappointed to see that it currently supports only a single time dimension per table and that that dimension is "hard-coded" to be the time of record entry. While this makes sense as a first priority because of its importance to the audit function, many of the most interesting "time travel" type queries involve "effective dates" that have nothing to do with the date on which a record was entered.
Are there plans to extend temporal data support to multiple (or at least two) dimensions per table?