Menu
I'm looking at SQL Server 2016 temporal tables and can't find any efficient way to query for all historical records that are now deleted.
I prefer not to soft-delete or moving to a 'deleted items table', as I feel with temporal tables it is redundant.
Can this can be achieved with temporal tables in an efficient way?
799
To delete data from a system-period temporal table, use the DELETE FROM statement. For example, the owner of policy B345 decides to cancel insurance coverage. The data was deleted on September 1, 2011 (2011-09-01) from the table that was updated in the Updating data in a system-period temporal table topic.
Recover Deleted Data in SQL Server Using LSN: LSNs (Log Sequence Numbers) are special identifiers that are allocated to each record in the SQL Server exchange logs. Subsequently, erased columns of SQL tables are recoverable if the time of their deletion is known.
Temporal tables are intended to give you a point-in-time view of your data, not a state view - it doesn't actually understand state. Nothing is exposed to users to determine how a row arrived in the temporal history table.
If you did not temporarily pause/stop system versioning on your temporal table then you just need to find the delta between the history table and the active table. All remaining rows in the history table that don't have a corresponding row in the active table are deleted rows.
For example, if you have tblCustCalls and it's enabled for temporal with a tblCustCallsHistory, something like SELECT * FROM tblCustCallsHistory WHERE ID NOT IN (SELECT ID FROM tblCustCalls). In this example, ID is the primary key. You can optimize the TSQL if the tables are very large but the base concept doesn't change.
160
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
