Question :1 Server : sql server 2008 R2 a) I have "prestaging" table as source table. b) I kept working table and History table as other set. c) The data from "prestaging" tables will move to both "working tables" and "History tables". d) The working table record have few validation is there after validated then all the successful record will move to the "TARGET" table. d) There is possibility of the old data can get amended from source system and then they will send the amended record in the "Prestaging tables " with the UNIQUE no column as primary key. d) Before inserting in to target table I need to compare the history table if the same record is appearing again based on the unique column then I need to Update "Modeflag" column as update record else the record will be send as "Inserted" d) There is possibilities of comparison of history table will have more than 500000 of records. d) Currently we are comparing with inner join with UNIQUE no as clusterered index column and then updating the mode-flag .
we required best possibilities way to avoid performance issue to handle this situation . Kindly provide the best solution to compare the history record .
Note : The history table will grow like anything in upcoming years.
Note :THE BOVE ALL LOGIC WRITTEN ON ONE STROED PROCEDURE AND THEN SQL JOB WILL EXECUTE ON EVERYDAY.