Comparing two rows for the same table multiple times

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.