Hi all ,
Temporal table is one of the main new features added in sql standard 2011. From that I will like to implement system versioned temporal table which allows to keep past and present data so old data can be queried. Am propose to implement it like below
CREATE
In create table only one table is create and both historical and current data will be store in it. In order to make history and current data co-exist row end time column will be added implicitly to primary key. Regarding performance one can partition the table by row end time column order to make history data didn't slowed performance.
INSERT
In insert row start time column and row end time column behave like a kind of generated stored column except they store current transaction time and highest value supported by the data type which is +infinity respectively.
DELETE and UPDATE
The old data is inserted with row end time column seated to current transaction time
SELECT
If the query didn’t contain a filter condition that include system time column, a filter condition will be added in early optimization that filter history data.
Attached is WIP patch that implemented just the above and done on top of commit b8e19b932a99a7eb5a. Temporal clause didn’t implemented yet so one can use regular filter condition for the time being
NOTE: I implement sql standard syntax except it is PERIOD FOR SYSTEM TIME rather than PERIOD FOR SYSTEM_TIME in CREATE TABLE statement and system time is not selected unless explicitly asked
Any enlightenment?
regards
Surafel