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.
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.