What is the best way to query deleted records with SQL Server 2016 temporal tables?

Question

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?

Staminody

Answer 1

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.

Kolyma

Thanks. I was hoping not to do a comparison. Your explanation state makes sense. – Staminody

Answer 2

There is a way to detect it via the ValidTo column of your temporal table.

The latest ValidTo for the record will be less than the current date.

Or another way to look at it, an undeleted record will have a ValidTo that equals '9999-12-31 18:59:59.9900000'. I don't trust this value enough to hard code looking for it, so I just look for ValidTo > current date.

Don't forget it's UTC.

I write in the last updated by user id on the record before I delete it so that essentially becomes a snapshot of who deleted it and when.

Rebekah

Just on your last point; doesn't the "ValidTo" serve as an inherent "LastModified"? – Staminody

You are correct. Thank you for pointing that out. – Rebekah

Answer 3

You could also add a column [Action] containing the action. This resolves in the following process: - Adding a new row: just add the row with [Action] = 'Inserted' - Updating an existing row: just update the row with [action] = 'Updated' - Deleting a row: First update the row with [Action] = 'Deleted' and delete the row

Like this you can find easily the unchanged rows in your basetable (where [action] = 'Inserted') and the deleted rows in your historytable (where [action] = 'Deleted')

Be aware this will create 2 rows in the history table!! (1 update and 1 delete statement)

Plantar