• 1. If you are already joining on the Unique No column, which is the clustered index, I don't see why partitioning the table on another column would improve performance (if it does, please enlighten me, always ready to learn)

    2. Why would inner join be more best practice than a left join? A join is a join and you use the right type of join to get the results you want. A left join tells you which rows are updates (join matched) and which rows are inserts (join did not match, so columns of right table are NULL). An inner join only tells you which rows of the left table are present in the right table, so it gives you only the updates.

    3. MERGE will take your source table and merge it into your destination table. Your scenario however is that you have a source table, a destination table and a lookup table (the history table). The lookup table tells you which rows are updates and inserts, so you kind of have a different scenario.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP