What are the uses of temporal tables?
Use cases for temporal tables include:
- Auditing all data changes and performing data forensics when necessary.
- Reconstructing state of the data as of any time in the past.
- Calculating trends over time.
- Maintaining a slowly changing dimension for decision support applications.
How do you convert a table to a temporal table?
Convert existing table to temporal table – Step by step
- Define a primary key on the table.
- Add start time and end time period columns in the table.
- Update row effective from and to period values for existing rows.
- Alter column to add NOT NULL constraint.
- Declare system period columns.
- Enable system versioning on the table.
Where are old values stored in a temporal table?
When updating a row in a temporal table the old values for the row are stored in the history table with the transaction begin time as the SysEndTime. The new values in the current table will have the transaction begin time as the SysStartTime.
Are there any problems with a temporal table?
The problems that can arise with temporal tables because of this are fairly severe; the scenario in your example is mild compared to what can go wrong in general: Broken foreign key references: Suppose we have two temporal tables, with table A having a foreign key reference to table B.
When to use sysendtime and sysstarttime in a temporal table?
The new values in the current table will have the transaction begin time as the SysStartTime. SysStartTime and SysEndTime are datetime2 columns used by temporal tables to record when a row was the current version. Transaction begin time is the time the transaction containing the updates started.
Where can I get SQL Server transaction begin time from?
I am working with temporal tables in SQL Server and the generated time is the transaction begin time in UTC datetime2 Is there a function or table in SQL Server to get that same transaction begin time to use elsewhere that does not require me to write to and select from a system versioned table first?