Now, assume that the price for the product has been changed to 200 and that this change was entered into the database on 28th November 2017. Here is the code for this action:
UPDATE dbo.Product SET Price = 200.00 WHERE ProductId = 1;-- on 28th November
The state of the current table is as follows after the preceding statement's execution:
ProductId | ProductName | Price | ValidFrom | ValidTo |
1 | Fog | 200 | 28.11.2017 | 31.12.9999 |
The state of the history table is as follows after the preceding statement's execution:
ProductId | ProductName | Price | ValidFrom | ValidTo |
1 | Fog | 150 | 12.11.2017 | 28.11.2017 |
Note again that values in the ValidFrom and ValidTo columns are displayed in short format for clarity. The value in the ValidTo column in the history table is identical to the ValidFrom value in the current table; there are no gaps.
Now, assume that you reduced the price the next day to 180. Here is the code for this action:
UPDATE dbo.Product SET Price = 180.00 WHERE ProductId = 1;-- on 29th November
The state of the current table is as follows after the preceding statement's execution:
ProductId |
ProductName |
Price |
ValidFrom |
ValidTo |
1 |
Fog |
180 |
29.11.2017 |
31.12.9999 |
The state of the history table is as follows after the preceding statement's execution:
ProductId |
ProductName |
Price |
ValidFrom |
ValidTo |
1 |
Fog |
150 |
12.11.2017 |
28.11.2017 |
1 |
Fog |
200 |
28.11.2017 |
29.11.2017 |
You can see another entry in the history table indicating that the price 200 was valid for one day. What would happen if you execute the same statement again, say on 30th November? There is no real change; no business logic attributes are changed, but what does it mean for temporal tables? Here is the code for this action:
UPDATE dbo.Product SET Price = 180.00 WHERE ProductId = 1;-- on 30th November
The state of the current table is as follows after the preceding statement's execution:
ProductId |
ProductName |
Price |
ValidFrom |
ValidTo |
1 |
Fog |
180 |
30.11.2017 |
31.12.9999 |
The state of the history table is as follows after the preceding statement's execution:
ProductId |
ProductName |
Price |
ValidFrom |
ValidTo |
1 |
Fog |
150 |
12.11.2017 |
28.11.2017 |
1 |
Fog |
200 |
28.11.2017 |
29.11.2017 |
1 |
Fog |
180 |
29.11.2017 |
30.11.2017 |
As you can see in the history table, even if there is no real change to the attributes in the current table, an entry in the history table is created and period date columns are updated.
Here is how an UPDATE of a single row in a temporal table affects the current and history tables:
- Current table: Values in the current table are updated to those provided by the UPDATE statement, the period start date column is set to the system date, and the period end date column is set to the maximum value for the DATETIME2 data type
- History table: The row from the current table before updating is copied to the history table, and only the period end date column is set to the system date
You can also see that there are no gaps in the dates in the same row of the history table. Even duplicates are possible; the history table does not have constraints to prevent them! Therefore, it is possible to have multiple records for the same row with the same values in period columns. Moreover, even values in period columns can be identical! The only constraint that is enforced is that the date representing the period end date column cannot be before the date representing the period start date (therefore, it is guaranteed that ValidFrom <= ValidTo).