I have an update trigger with the following code fragment. The performance degrades as the number of rows being updated increases since it will do N table scans.
INSERT INTO MYTABLE_HIST
SELECT dl.S_ID, dl.NAME_NOTATION, ............ dl.RECORD_STATUS
FROM deleted dl
JOIN inserted ir
ON ir.SC_ID = dl.SC_ID
WHERE ir.SU_ID < 1000
I would like to coerce it into using a SORT MERGE Join but am just now looking into abstract plans. Has anybody got any idea how to make this happen? SC_ID is a unique identifier but, of course, there are no indexes on the Deleted and Inserted pseudo tables in a trigger.
Thanks in advance,