Visual Studio Data Comparison with less columns

Question

My project currently has a database which contains several tables, the most important of which has one binary column with very large entries (representing serialized C# objects). There are a large number of entries in the production database, and when debugging, it is often necessary to pull these entries down into the local development database (as remote debugging does not seem to work, which is a separate issue).

If I attempt to compare the local and production databases on this table with all columns, the comparison can take up to an hour, or eventually time out, but this has worked in the past and allowed me to download the entries and debug them successfully. If I compare on all table columns but the binary data column, the comparison is almost instantaneous, but that column is not then transferred to the production database.

My question is: is there any way to run a data comparison between two tables, excluding a particular column for the comparison itself (other fields give enough information to differentiate without it) but including it when updating the target database?

Scarabaeid

Answer 1

You could use a hash function on your large varbinary fields and compare those. HASHBYTES with MD5 is a good method for comparing as it's astronomically unlikely to generate the same hash value for two different inputs. Problem is, HASHBYTES only works on fields up to 8000 bytes. There are some work arounds though by creating a function. A few posted here:

SQL Server 2008 and HashBytes

You would have the option of storing the hash values in your table at the time of insert or update by using a persisted calculated fields. Or you could just generate the hash values while doing your comparison query.

Flagging