I recently presented a webinar on temporal tables in SQL Server 2016. I want to use this post to make my presentation materials available, and to answer questions I was not able to get to during the talk.
Following is a link to the recording of the webinar: Using SQL Server 2016 Temporal Tables for Data Forensics and Auditing
The slides I presented are available here: SQL Server 2016 Temporal Tables PPTX
The SQL scripts I used in the demos are available here: Temporal Tables Sample Scripts
Following are my responses to questions I did not address in the session.
Q: When versioning is disabled, do the start/end date fields remain?
Q: Is historical data lost when SQL Server restarts?
A: No. History tables store data just like a regular table.
Q: How long is history data stored?
A: There is no built in mechanism for managing historical data retention. Historical data will be stored until it is removed by a query or the history table is removed.
Q: If you add a new column with a DEFAULT, will the default value be added to the history or will it be NULL?
A: The default value is populated in both the current and history table.
Q: Does turning off the system versioning mean losing new history until it is turned back on?
A: Any data changes that occur while system versioning is disabled will not be recorded.
Q: Can temporal tables include the name/user ID of the person who made the change.
A: Unfortunately, no. System versioned tables only record effective dates.
Q: Can you define an auto-retention policy on the history table, such as only keep last N months, or last N records per key?
A: There are no built in tools or settings to manage retention of historical data. This type of custom retention policy could be implemented, though.
Q: I have existing history kept separately, manually, can I pre-populate the history table with that data?
A: Yes. You’ll have to consider a few things. The history table must have the same fields and data types as the current data table. The start/end dates must use DATETIME2 data type. I’d recommend including DATA_CONSISTENCY_CHECK when enabling system versioning with your existing history table. This command ensures start/end dates ranges do no overlap. Read more on DATA_CONSISTENCY_CHECK here: MSDN – Temporal Table System Consistency Checks
Q: Are temporal tables a replacement for Change Data Capture (CDC)?
A: No. The uses cases for these features vary slightly. If you are considering CDC, I would try temporal tables first since they are simple to implement, and simple to remove if no longer needed.
Q: How many temporal tables can I create?
A: There is no limit on the number of temporal tables in a database.
Q: What techniques are available for managing historical data.
A: The following MSDN article describes various techniques for managing historical data, including partitioning, using Stretch Database, and custom scripts. MSDN – Manage Retention of Historical Data in System-Versioned Temporal Tables
Q: What impact does truncation of the current table have on the history table?
A: Temporal tables cannot be truncated. Using the TRUNCATE command on a temporal table will fail.
Q: In your demo, what happened to the data that was tracked when you eliminated the history tracking?
A: Nothing. Disabling system versioning does not remove previously captured history. Disabling system versioning only stops the recording of new changes.
Q: Can a temporal table use a composite primary key?
Q: Can the history table be created in another schema?
A: Yes, as long as it’s in the same database.
Q: Can you update the primary key field in a temporal table?
A: Yes. However, this will likely cause issues for you when querying historical data. If a PK value is updated from “AA” to “BB” for example, the “AA” record will be recorded in the history table with an end date. But now there is no way to correlate the “BB” row in the current table to the “AA” records in the history table. In effect, it may look as if the “AA” record was deleted from the current table.
Q: How can I determine which rows were deleted from a temporal table?
A: By finding a list of primary key values in the history table that do not exist in the current table.
Q: If I create an index on a temporal table, will a similar index be added to the history table?
Q: Are a user’s security privileges on a history table the same as on the current table?
A: Not necessarily. Permissions on the current and history table can be changed independent of each other. A user requires SELECT permission on the history table to read from that table. If a user has SELECT permission on a current table, this does not mean the user automatically has the same permission on the history table.
Q: Is there overhead associated with using temporal tables?
A: Yes. Update and Delete operations include an operation for inserting into the history table. Adjusting the history table can help reduce the overhead incurred with the extra operation. As is usually the case, adding an index to a table to improve read/SELECT performance will generally reduce performance of INSERTs on that table. Any quantification of overhead has many variables, including row size, data types, and indexes in place.