Geek Answers Handbook

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

I am looking at temporary tables in SQL Server 2016 and cannot find an efficient query method for all historical records that are now deleted.

I prefer not to gently delete or move to the deleted items table, as I feel that temporary tables are redundant.

Can this be achieved using temporary tables in an efficient way?

+7
source share
2 answers

Temporary tables are designed to give you accurate information about your data, and not about the state - this does not actually understand the state. Nothing lets users understand how the row arrived in the time history table.

If you do not temporarily suspend / stop system version control in your temporary table, you just need to find the delta between the history table and the active table. All other rows in the history table that do not have a corresponding row in the active table are deleted rows.

For example, if you have tblCustCalls and it is enabled for temporary with tblCustCallsHistory, then it seems to be SELECT * FROM tblCustCallsHistory WHERE ID NOT IN (SELECT ID FROM tblCustCalls) . In this example, the identifier is the primary key. You can optimize TSQL if the tables are very large, but the basic concept does not change.

+10
source

There is a way to detect this through the ValidTo column of your temporary table.

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

Or, if you look at it from the other side, then for an unrecovered record there will be ValidTo equal to "9999-12-31 18: 59: 59.9900000". I don't trust this value enough for the hard code to look for it, so I'm just looking for ValidTo> current date.

Do not forget UTC.

I record the last record updated by the user ID in the record before deleting it, so that, in essence, it becomes a snapshot of the person who deleted it and when.

+1
source

More articles:


All Articles