I had a need to compare two SQL Server table for differences between them.  I started using .NET dataset features (merge, acceptchanges, getchanges) as follows:

There are a few gotcha's with the above code.  The primary problem was that both tables must have primary keys defined.  I figured ok, I could create primary keys through code for the related DataTables however I soon realized that there were duplicate rows within the tables. SQL Server 2005 has Except and Intersect functions (http://msdn.microsoft.com/en-us/library/ms188055(SQL.90).aspx) that return distinct values by comparing the results of two queries.  The entire row is compared against another row from another table.

Except returns any distinct values from the left query that are not found on the right query. Intersect returns any distinct values that are returned by both the query on the left and right sides. In order to use the number and order of the columns must be the same in the queries and also the data types must be comparable.  To return all rows in table1 that do not match exactly the rows in table2, you can use Except ... select * from table1 except select * from table2

(likewise to find the opposite just reverse the table names above)

To return all rows in table1 that match exactly what is in table2, using Intersect... select * from table1 intersect select * from table2

Combining the above two... (the following will return the differences)

select 'table1' as tblName, *  from
  (select * from Table1 except select * from Table2) x
union all
select 'table2' as tblName, *  from
  (select * from Table2 except select *  from Table1 ) x

If you are fortunate to have primary keys you can of course still use IN/NOT IN type queries however it seems that performance is much improved with the Except/Intersect approach.