多次比较同一个表的两行
我有一个包含快照数据的 SQLServer 表。我需要将最新行与前一行进行比较，并确定某些列是否已更改。我需要多次比较不同的列组合，一次最多 40 个。每当组合之一中的列值有所不同时，我都需要创建一个包含最新值的 xml 文档。我希望每一行至少生成一个 xml 文档。
执行比较的最佳位置在哪里，我应该在存储过程中执行此操作，每个列组合一个。或者我应该通过 ADO.NET 拉回整行并在代码中比较它们？有没有一种简单的方法可以进行比较？
I have a SQLServer table that contains snapshot data. I need to compare the latest row against the previous and determine if certain columns have changed. I need to do this several times comparing different combinations of columns, up to 40 at a time. Every time a column value differs within one of the combinations I need to create an xml document containing the latest values. I expect every row to produce at least one xml document.
Where is the best place to perform the comparison, should I do this in stored proceedures, one for every combination of columns. Or should I pull back the whole rows via ADO.NET and compare them in code? Is there an easy way of doing the comparison?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您没有提供足够的详细信息（DDL、示例等）来知道这是否是您想要做的，但是......
在过去的情况下，我会在该表上创建一个触发器并将更改日志写入另一个表。然后读取、处理并删除这些排队的更改条目。
在触发器中，您可以查询列的状态以查看它是否已更新。
You didn't give enough details (DDL, examples, etc) to know if this is what you want to do but ...
In situations like this in the past I would create a trigger on that table and write change logs to another table. Then read, process, and delete those queued change entries.
In a trigger you have the ability to query the column's status to see if it has been updated.
通过 ADO.Net 比较行起初看起来很流畅，但很快就会变得陈旧。随着数据量的增加，您的执行时间也会增加。
SQL Server 内的比较是对此的最佳选择。
Comparing rows via ADO.Net seems slick at first, but can get old really quickly. As the volume of data goes up, your execution time will go up as well.
Comparisons within SQL Server is your best bet for this.
一般，完成您所描述的工作的最佳（即最快）位置是尽可能靠近数据，即在数据库本身中。
也就是说，我想知道为什么你要这样做，以及是否可以从另一个角度解决这个问题。
In general, the best (i.e. fastest) place to do the work you're describing is as close to the data as possible, i.e. in the database itself.
That said, I wonder why you're doing it this way and if it's possible to attack the problem from another angle.