SQL Server 临时表创建重复记录 [英] SQL Server Temporal Table Creating Duplicate Records

问题描述

我在我们的一个经常更新的生产表上实施了一个系统版本(临时)表.此表由应用程序、高级用户(手动使用自定义脚本和标准化 sproc)和系统作业更新.

I have implemented a system versioned (temporal) table on one of our production tables that is frequently updated. This table is updated by applications, power users (manually with custom scripts and standardized sprocs), and system jobs.

我刚刚遇到了一种情况，我看到一条新记录插入到历史记录表中，但它看起来与之前的记录完全相同.当我对此进行研究时，我发现这不是一次性的情况；还有更多.请记住，开始日期和结束日期不会重复.上一条记录的结束日期与下一条记录的开始日期一致，其他所有数据完全相同.

I have just run into a situation where I see a new record inserted into the history table but it appears exactly the same as the one before it. As I research this I see it is not a one off situation; there are many more. Please keep in mind the begin and end dates are not duplicated. The end date of the previous record matches the begin date of the next record, with all other data being exactly the same.

如果可能的话，我想防止这种情况发生，但首先我需要知道这是从哪里来的.

I would like to prevent this from happening if possible but first I need to know where this is coming from.

有人遇到过这种情况吗?有没有人足够了解时态表来给我一些潜在的线索，可以触发插入历史表而不对数据进行可见更改?是否可以在不更改用户可见字段的情况下更改记录?

Does anyone have experience with this happening? Does anyone understand temporal tables enough to give me some potential leads on what could trigger an insert into the history table without a visible change to the data? Is it possible to change a record without changing the fields visible to the user?

提前致谢.

推荐答案

我很确定表的更新实际上不会更改数据但仍然设置它(即更新表集 field1 = field1)将导致历史表中的新条目.

I'm pretty sure an update of the table that doesn't actually change data but still sets it (ie, update table set field1 = field1) will cause a new entry in the history table.

我猜它是由高级用户"脚本引起的(在我的历史中，它往往是第 1 位错误代码的来源)，尽管应用程序也可能负责.可能在代码审核"时间范围内，以找出更新的来源.

I'm going to guess it's being caused by 'power users' scripts (tends to be the number 1 source of screwy code from my history), though the application may also be responsible. Might be in the range of 'code audit' time to find out where the update is coming from.

这篇关于SQL Server 临时表创建重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！