Using Set operations (SQL) to detect differences between two tables

Identifying differences between two tables can become quite a daunting task.

Using SQL set operations like {UNION,INTERSECT,EXCEPT} can greatly simply this is process.

Any attempt to use compare operations on .Net runtime turns out to be very costly affair for these operations.

 

I tried creating a set operations. Here my intention is to pass two tables which share same schema, then pass on ID field which

is anchor. This results a single table specifying Addedd/Changed/Deleted/UnChanged records. That makes whole set.

SELECT t1.*, 'Added'FROMTable1 t1
LEFT JOINTable2 t2 ONt1.ID = t2.ID
WHEREt2.ID IS NULL

union

SELECTt1.*,'Changed'
FROMTable1 t1
INNER JOINTable2 t2 ONt1.ID = t2.ID
except
SELECTt2.*,'Changed'
FROMTable1 t1
INNER JOINTable2 t2 ONt1.ID = t2.ID

union

SELECTt2.*, 'Deleted'
FROMTable1 t1
RIGHT JOINTable2 t2 ONt1.ID = t2.ID
WHEREt1.ID IS NULL

union

SELECTt1.*,'UnChanged'
FROMTable1 t1
INNER JOINTable2 t2 ONt1.ID = t2.ID
INTERSECT
SELECTt2.*,'UnChanged'
FROMTable1 t1
INNER JOINTable2 t2 ONt1.ID = t2.ID

ORDER BY XX

I tried this with two tables each having 100 columns and 5000 rows. I could detect above mentioned changes like Added/Changed/Deleted/UnChanged within 18 seconds.

Thus I believe set operations can offer much cleaner and simpler solution in such scenarios.