When I figured about this tool a while ago, I was same.. WOW.. And we just ended up using this tool at one of our DW implementation for change detection. It works fine but wouldn't be using it in future projects. I think EXCEPT function can be much better and gives you more control on what can be done. From my experience, what I have found this tool has got some limitations as below (but that could be just my limited knowledge about this tool):

1. Requires primary key definition (as stated in article, so no good with heap tables)

2. If no difference's detected, the diff table (assuming you are sending differences to table) will not be updated with no rows. Diff table will still show the differences from last comparison. This behavior got me for some time

3. Diff table inherits the schema of user executing the command. (This can be an issue where generic account is used. We had to run extra T-SQL to change the schema. Would be easier if we could assign default schema to AD groups which is kind of a bug at the moment with MSSQL)

I think for adhoc requirements this tool can be good but so as EXCEPT where I don't have to remember the syntax. :hehe:

Regards,

- Harish