Current advice for the most efficient way to compare two large result/row sets seems to be to use the
EXCEPT operator. This self contained SQL script below gets very inefficient as row sizes increase (change @last values). I have tried to find unique entries in a combined table but with no improvement.
DECLARE @first AS INT, @step AS INT, @last AS INT; -- This script is comparing two record sets using EXCEPT -- I want to find additions from OLD to NEW -- As number of rows increase performance gets terrible -- I don't have to use two tables. I could use one combined table but I want the same result as quickly as possible -- Compare 100 to 110 rows - 0 seconds -- Compare 1000 to 1010 rows - 1 seconds -- Compare 10000 to 10010 rows - 16 seconds -- Compare 100000 to 100010 rows - ABORT after 8 minutes (tables are populated in 18 seconds) DECLARE @temptableOLD TABLE ([Result1] int); SET @step = 1; SET @first = 1; SET @last = 100000 WHILE(@first <= @last) BEGIN INSERT INTO @temptableOLD VALUES(@first) SET @first += @step END DECLARE @temptableNEW TABLE ([Result1] int); SET @step = 1; SET @first = 1; SET @last = 100010 WHILE(@first <= @last) BEGIN INSERT INTO @temptableNEW VALUES(@first) SET @first += @step END select * from @temptableNEW except select * from @temptableOLD