I'm looking for the fastest way of testing two databases for data equality. The use case is automated testing where I take a database, perform an operation on it, and want to compare it with a "known good" baseline database. I know I could use SQL Data Compare, but this feels like overkill, and I'm hoping there's a way of doing it much faster, so it's not prohibitive on huge databases.
If the databases are huge the comparison of data will take a lot of time. I have done similar things, though with pretty small datasets. In those cases I have used T-SQL-scripts with some joins, some EXCEPT/INTERCEPT etc. I could also Think if using SSIS to make up a diff between two tables. But I´m pretty sure Redgate's SQL Data Compare is faster. If it's just a small subset of the data you want to compare, then I'd just use T-SQL. No matter how it's done, you WILL make comparison between columns which are not indexed and that is going to be relatively slow.