在 SQL Server 中审核数据和检索时间点数据的最佳实践
我在数据库中制作历史表已经有一段时间了，但从未投入太多精力或思考。我想知道最好的做法是什么。
我的主要目标是记录特定日期记录的任何更改。如果一天内发生多次更改，则只会存在一条历史记录。我需要记录记录更改的日期，而且当我检索数据时，我需要从历史记录中提取特定时间的正确记录。例如，我有一个客户表，想要提取他们在特定日期的地址。我的存储过程（如获取客户详细信息）将接受可选日期，如果没有传入日期，则它将返回最新记录。
所以这就是我正在寻找的建议：
我是否将历史表保留在同一个表中并使用逻辑删除标志来隐藏历史表？我通常不会这样做，因为有些表可能会发生很大的变化并且有很多记录。我是否使用反映主表的单独表？我通常这样做。我应该只将更改记录放入历史表而不是当前表中吗？给定一个日期，在某个时间点提取正确的记录，获取客户 <= 传入日期的每条记录，然后按最近的日期排序并取顶部，最有效的方法是什么？
感谢您的所有帮助...问候 M
I've been doing history tables for some time now in databases, but never put too much effort or thought into it. I wonder what is the best practice out there.
My main goal is to record any changes to a record for a particular day. If more than one change happens in a day then then only one history record will exist. I need to record the date the record was changed, also when I retrieve data I need to pull the correct record from history as it was at a particular time. So for example I have a customers table and want to pull out what their address was for a particular date. My Sprocs like get Cust details will take in an optional date and if no date is passed in then it returns the most recent record.
So here's what I was looking for advice on:
Do I keep the history table in the same table and use a logical delete flag to hide the historical ones? I normally don't do this as some tables can change a lot and have lots of records. Do I use a separate table that mirrors the main table? I usually do this. Should I only put change records into the history table and not the current one? What is the most efficient way given a date to pull out the right record at a point in time, get every record for a customer <= date passed in, and then sort by most recent date and take the top?
Thanks for all the help... regards M
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
建议使用基于触发器的审计并为需要审计的所有表创建触发器。
使用触发器，您可以满足每天不存储多于一条记录更新的要求。
我建议您查看为您生成触发器的 ApexSQL Audit 并尝试对触发器进行逆向工程他们使用什么，存储表是什么样子等等。
这将为您提供一个良好的开始，您可以在那里工作。
免责声明：不隶属于 ApexSQL，但我每天都会使用他们的工具。
Suggestion is to use trigger based auditing and create triggers for all tables you need to audit.
With triggers you can accomplish the requirement for not storing more than one record update per day.
I’d suggest you check out ApexSQL Audit that generates triggers for you and try to reverse engineer what triggers they use, how storage tables look like and such.
This will give you a good start and you can work form there.
Disclaimer: not affiliated with ApexSQL but I do use their tools on a daily basis.
我不是该领域的专家，但一位优秀的 SQL 顾问曾经告诉我，如果所有数据都可以更改，那么通常使用同一个表是一个好的方法。否则，原始表仅包含核心不可更改的数据，而历史表仅包含可以更改的内容。
I'm no expert in the field but a good sql consultant once told me that a good aproach is generally to use the same table if all data can be changed. Otherwise have the original table contain only core nonchangable data and the historical table contain only stuff that can be changed.
您绝对应该阅读这篇文章 管理双时态数据。这种方法的好处是它提供了一种可审计的方式来纠正历史数据。
我相信这将解决您对修改历史数据的担忧
You should defintely read this article on managing bitemporal data. The nice thing about this approach is it enables an auditable way of correcting historical data.
I beleive this will address your concerns about modidying the history data
我一直使用 本文。虽然它确实需要您对数据进行透视，使其类似于表的本机结构，但它可以适应架构的更改。
您可以创建一个返回表并接受表名称 (
varchar) 和时间点 (datetime) 作为参数的 UDF。 UDF 应使用审计（历史值）重建表，并为您提供该日期和时间的有效值。时间。I've always used a modified version of the audit table described in this article. While it does require you to pivot data so that it resembles your table's native structure, it is resilient against changes to the schema.
You can create a UDF that returns a table and accepts a table name (
varchar) and point in time (datetime) as parameters. The UDF should rebuild the table using the audit (historical values) and give you the effective values at that date & time.