-
Notifications
You must be signed in to change notification settings - Fork 5
Home
System versioning is defined by SQL:2011.
System versioned tables contain historical data for all transactions ever run
against the table. Consider following example (note the new syntax extensions
with system versioning):
> create table t(x int) with system versioning;
> insert into t values (1);
> update t set x=2;
> delete from t;Now the table t doesn't have any data:
> select * from t;
Empty set (0.00 sec)However, the system versioned table stores all versions of the row and we can query any of them:
> select * from t for system_time all;
+------+---------------+-------------+
| x | sys_trx_start | sys_trx_end |
+------+---------------+-------------+
| 2 | 2323 | 2330 |
| 1 | 2320 | 2323 |
+------+---------------+-------------+Note the invisible columns sys_trx_start and sys_trx_end. The columns contain
transaction IDs for the appropriate row versions: sys_trx_start describes which
transaction created the row version and sys_trx_end describes which
transaction deleted the row version. Besides the new hidden columns, the example
above uses for system_time syntax extension, which is also new.
This is the most trivial example. Of course you can run much more complicated historical queries.
There is good description of Temporal Table Usage Scenarios from Microsoft. In short the feature is useful for:
-
Point-in-time recovery - recover database state as of particular point in time;
-
Forensic discovery & legal requirements to store data for N years
-
Data analysis (retrospective, trends etc.), e.g. to get your staff information as of one year ago.
MariaDB Flashback was introduced in 10.2.4 and is based on binlogs, so it's limited by binlog size and has lower performance than System versioning. Also System versioning provides reach SQL syntax extensions for querying historical data.