SQL Server 2008 - 检查行更改
我没有使用大量的 or 语句来检查行是否已更改，而是查看了 checksum() 或 binary_checksum()。对于这种情况，最佳做法是什么？是使用 checksum()、binary_checksum() 还是其他方法？我喜欢使用校验和选项的想法，这样我就不必为我的更新构建大量的 or 语句。
编辑： 对不起大家，我应该提供更多细节。我需要从一些外部来源提取数据，但因为我正在使用合并复制，所以我不想只是井喷并重建表。我只想更新或插入确实发生更改或不存在的行。我将在目标数据库中拥有源数据的配对版本，该版本将同步到客户端。我试图找到一种好方法来检测行更改，而不必查看每一列来执行更新。
非常感谢任何建议。
谢谢，
S
Instead of using a ton of or statements to check if a row has been altered I was looking into checksum() or binary_checksum(). What is best practice for this situation? Is it using checksum(), binary_checksum() or some other method? I like the the idea of using one fo the checksum options so I don't have to build a massive or statement for my update.
EDIT:
Sorry everyone, I should have provided more detail. I need to pull in data from some outside sources, but because I am using merge replication I don't want to just blowout and rebuild the tables. I want to only update or insert the rows that really have changes or don't exist. I will have a paired down version of the source data in my target db that will get sync'd down to clients. I was trying to find a good way to detect the row changes without having to look at every single column to perform the update.
Any suggestions is greatly appreciated.
Thanks,
S
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
首先，如果您使用实际的合并复制，它应该负责为您更新正确的行。
其次，通常确定行是否已更改的方法是使用数据类型为
timestamp的列（现在称为rowversion），每次行更新时该列都会更改。但是，这种类型的列只会告诉您自上次读取该值以来该值是否发生变化，这意味着您必须读取并存储时间戳才能进行比较。因此，这可能不适合您。最后，可能适合您的解决方案是相关表上的触发器，该触发器用当前日期和时间更新实际的
DateTime（或者更好的是DateTime2）列当插入发生时。您的比较需要存储上次同步到表的日期时间，并比较上次更新列中的日期时间以确定哪些行已更改。First, if you are using actual Merge replication, it should take care of updating the proper rows for you.
Second, typically the way to determine if a row has changed is to use a column with a data type of
timestamp, now calledrowversion, which changes each time the row updated. However, this type of column will only tell you if the value changed since the last time you read the value which means you have to have read and stored the timestamps to use in comparison. Thus, this may not work for you.Lastly, a solution which may work for you would be triggers on the table in question that update an actual
DateTime(or better yet,DateTime2) column with the current date and time when an insert takes place. Your comparison would need to store the datetime you last synchronized to the table and compare that datetime in the last updated column to determine which rows had changed.如果我们有更多关于您正在做什么的信息可能会有所帮助，但一般来说，只要您有权访问要比较的行的原始校验和， checksum() 选项就可以很好地工作。
It might help if we have a bit more info about what you are doing but in general the checksum() option does work well as long as you have access to the original checksum of the row to compare to.