I had the pleasure of presenting Temporal Tables to the Capital Area .NET User Group on December 10, 2024. Some interesting FAQ arose from that meeting so I thought it would be good to share it on my blog for reference.
- Is the historical table logged to the Transaction log in the same way a conventional table is? Will we “see” the inserts in the Tran log the same way we see them for a normal table?
No
- Are Temporal Tables available in Azure?
Yes, in Azure SQL Database and Azure SQL Managed Instance
- Will Temporal Tables work with graph tables?
No, Node and edge tables can't be created as system-versioned temporal tables
Ref: https://learn.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-architecture?view=sql-server-ver16
- What triggers the purge of SQL Server Temporal Tables?
A background task is created to perform aged data cleanup for all temporal tables with finite retention period.
ref: https://learn.microsoft.com/en-us/azure/azure-sql/database/temporal-tables-retention-policy?view=azuresql
- Can I modify recs in the history table with versioning OFF?
Yes
- Can I alter the current table with versioning ON?
Yes, alters both tables simultaneously
- Does EF Code First Support Temporal Tables (TT)?
Yes, EF Core 6.0 and later supports:
- The creation of temporal tables using Migrations
- Transformation of existing tables into temporal tables, again using Migrations
- Querying historical data
- Restoring data from some point in the past
- When are Temporal Tables NOT a good fit?
For Static data where a user can't change a field.
The following industries rarely, if ever, are users allowed to delete any data
- Financial services (i.e. stock purchases, banking)
- Purchases
- GPS Location Tracking
- Fraud Detection(Location of purchases, IP/location of login)
- Tracking appointments (Resolve Customer service issues)
- Car Rental company, tracking owner and mileage when vehicle was in possession
- Hotel room rental
- Airline seat charts, who sat where in which flight
- Are there other approaches are available for managing historical data in the temporal history table?
Yes, The following four methods are available:
- Stretch Database
- Table Partitioning
- Custom Cleanup Script
- Retention Policy
- Can Temporal tables be used with partitioned databases?
Yes, with some limitations. If current table is partitioned, the history table is created on default file group because partitioning configuration is not replicated automatically from the current table to the history table.
- Can the temporal table be placed in another DB?
No, History table must be created in the same database as the current table.
However, it can be placed in a different schema within the same database.
Also, Temporal querying over Linked Server is not supported.
- Can it be used with Elastic DBs?
Yes
- Can I alter table schema with sys versioning = on?
No
- Can I add additional fields to History table?
No, fields and field nullability must be identical
- Is there automatic truncation of history table?
Yes, using HISTORY_RETENTION_PERIOD = 6 MONTHS
