使用SQL Server 2016时态表查询已删除记录的最佳方法是什么? [英] What is the best way to query deleted records with SQL Server 2016 temporal tables?

问题描述

我正在查看SQL Server 2016时态表，找不到任何有效的方法来查询现在已删除的所有历史记录.

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?

推荐答案

临时表旨在为您提供数据的时间点视图，而不是状态视图-它实际上无法理解状态.没有任何内容可供用户确定行如何到达时间历史表.

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.

例如，如果您有tblCustCalls，并且已使用tblCustCallsHistory将其启用为临时性，则类似于SELECT * FROM tblCustCallsHistory WHERE ID NOT IN (SELECT ID FROM tblCustCalls).在此示例中，ID是主键.如果表很大但基本概念没有改变，则可以优化TSQL.

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.

这篇关于使用SQL Server 2016时态表查询已删除记录的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！