SQL Server Performance Forum – Threads Archive
Compare rowsHi everyone First of all the situation I have: I have a table with lets say 40 columns(maybe more). Data in the table is synchronized sometime. Synchronizing every hour I have less that 5% changed rows. The question is quite simple:
How can I compare rows to know that the same row (saying the same I mean have the same primary key) has not changed since last synch before updating/adding rows in table? Maybe store some kind of hash column to compare rows? I though to write some function that will calculate some unique value based on column values, store this checksum in the synchronized table and check that value everytime.
Looks very expensive for me, isn’t? Course it’s possible to write a query like: UPDATE Table
Set c1 = Table1.c1,
cx = Table1.cx
INNER JOIN Table1
ON Table.PrimaryKey = Table1.PrimaryKey
WHERE Table.c1 <> Table1.c1 OR
Table.cx = Table1.cx But i don’t like such solutions because of many reasons – for example WHERE consists of many OR conditons is already very unpleasant thing… I have no possibility to add UpdatedDate column or so. The problem is the database I’m taking data from, has no time in date values and information is aggregated on daily basis (nothing to do with that, that db does not belong to my company). Any help is kindly appreciated… Maksim Maksim
Can you add a trigger to the table, and add an audit table to the database? Trigger on update event of the data entry table to do this: insert into tblaudit (ident_col) select ident_col from inserted
where not exists (select ident_col from tblaudit where ident_col = inserted.ident_col) This will give you the identity values for all rows that have been updated. Add a datetime column, to be used by a job that you schedule to do the synchronization: the job starts by setting a datetime variable to GETDATE(), then copies that datetime onto all rows with no value on the column, then run the synchronization for the rows with the job’s datetime.