asked
Performance hit while Comparing Rows in absence of key
Hello, May anyone please help me on this. database: SQL SERVER 2008 18 Million records i have two table and i need to check missing rows between two table, as i don't have any keycolumn i concatenated all the column and called it as oldrow and newrow But my problem is this query tooks me more than 6 hours and still executing even after indexing done. Can anyone please suggest me some other solution using BINARY_CHECKSUM or HASHBYTES **Query I tried:** SELECT , OLD.Resistor , OLD.ResistorID , OLD.Capacitor , OLD.CapacitorID , OLD.Years ,CASE WHEN New.NewRow IS NULL THEN 'Record Deleted' ELSE 'No Change' END AS FixMessage FROM #oldfinal Old LEFT OUTER JOIN #newfinal New ON Old.OldRow = New.NewRow UNION SELECT , NEW.Resistor , NEW.ResistorID , NEW.Capacitor , NEW.CapacitorID , NEW.Years , 'Record Added' AS FixMessage FROM #newfinal New LEFT OUTER JOIN #oldfinal Old ON New.NewRow = Old.OldRow WHERE Old.OldRow IS NULL None of the column is unique, all contain duplicate value, i have added index but in vain will it be possible to include BINARY_CHECKSUM or HASHBYTES . Please share your expertise. Thanks,
(Select [column_list] from Table1 Except Select [column_list] from Table2) UNION (Select [column_list] from Table2 Except Select [column_list] from Table1) The above should help you and give you any deleted or added rows in both of the tables.
18 People are following this question.
