Sql Server 中的数据时间线性能
我有一个包含三列的表：
user varchar、status varchar、rep int
status 和rep 经常更新，其中一个要求是：
给定一个日期时间值查看状态和rep 值，对于每个用户在给定的日期。
我可以向表中添加一个 Updated_at 日期时间列，并插入同一用户的新行，而不是更新现有行，但是表会变大，我需要将查询结果连接到其他表的结果类似，因此我需要快速查询。
SQL Server 中解决此问题的最佳解决方案是什么？
I have a table with three columns:
user varchar, status varchar , rep int
status and rep get updated often and one of the requirements is:
Given a datetime value view status and rep values, for each user, at the given date.
I can add an updated_at datetime column to the table and insert new rows whith the same user instead of update existing ones, but the table gets large and I need to join the query results to similar results from other tables, thus I need a fast query.
What is the best performing solution to this problem in SQL Server?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
根据您将如何使用报告信息，最好创建一个历史表，在更新主表之前，将当前值写入历史表，并添加时间戳。
如果您真的只是从历史记录中挑选特定的日期/用户/状态，那就最好了
Depending on exactly how you are going to use the reporting information, it might be best to create a History table that before you update the main table, you write the current values off to the History table, adding the timestamp.
That would be best if you are really only picking out specific days/users/statuses from the history
我会将其存储为一个单独的相关表，因为我假设大多数时候您在加入时只会查询当前状态。 如果您要进行大量此类查询并将此类数据添加到许多表（而不仅仅是这个表），我将创建一个单独的 OLAP 数据库用于报告。
顺便说一句，只要您这样做，您可能会考虑是否也想记录谁进行了更改。 那么你基本上已经构建了一个审计解决方案。
I would store this is a separate related table since I assume most of the time you will only be querying the current status when you join. If you will be doing a lot of this type of querying and adding this type of data to many tables not just this one, I would create a separate OLAP database for reporting.
Incidentally as long as you are doing this, you might consider if you want to record who made the change as well. Then you have basically built an auditing solution.
一种选择是使用两个单独的表：“当前值”表和历史表。 当前值表将包含每个用户的唯一最新值，这将使其保持较小且易于连接。 历史记录表将包含您列出的三列以及时间戳列，并将跟踪值随时间的变化。 每当您想要更改当前值表中的值时，您都可以同时将包含新值的行添加到历史表中，并使用 gettime() 作为时间戳。
One option for this would be to use two separate tables: a "current values" table, and a history table. The current values table would contain the only latest values for each user, which would keep it small and easily-joinable. The history table would contain the three columns you listed as well as the timestamp column, and would track the changes to the values over time. Anytime you wanted to change a value in the current values table, you would simultaneously add a row with the new values to the history table, using gettime() as the timestamp.