Compare two tables having many columns using binary_checksum is not reliable method, any alternative?

Hi,

I want to compare two tables, each having 120 M records, both tables having 70 columns of various data types. I was looking for binary_checksum for comparision, I was assuming binary_checksum is representing one unique value for 70 columns of a perticular row, and I can compare two tables on the basis of only one column(binary checksum) instead of 70 columns but binary_checksum is not reliable. Given query is giving duplicate value(17761) for two different values.I am looking for any other alternative of doing this. Please reply for any better and reliable solution.

SELECT BINARY_CHECKSUM('ABA'), BINARY_CHECKSUM('ACQ')

Thanks,

Naveen

Did you try using CHECKSUM functions which is intended for these type of operation.

Yes prashant, MSDN itself is saying for small chances for duplicacy of checksum function. Please see "remarks" in following link.

http://msdn.microsoft.com/en-us/library/ms189788.aspx

That is why I tried for binary checksum , msdn is not putting any question mark on binary checksum , but I found problems in this and I have sent examples.

http://msdn.microsoft.com/en-us/library/aa225956(v=sql.80).aspx

Can I use combination of checksum and binary checksum?  Any other alternative?

Thanks,

Naveen

Hi Naveen,

In case you want to compare the tables and list all differences you can use TableDiff utility which comes with SQL Server. (Hope you are working on SQL Server 2005 or above)

I wrote an article explaining this step-by step in Oct'11, here's the link:http://www.sqlservergeeks.com/articles/sql-server-bi/74/compare-the-data-in-two-tables-without-any-3rd-party-tool

Hope this will help you.

Thanks.

Thanks Sarab for your quick response.

This is certainly a useful article. I want to know , if this utility can give records instead of set of queries, this will help me. Please let me know if you know any option for getting records having differences(without primary key).

Thanks and awaiting for your reply,

Naveen

Hi Naveen,

Unfortunately i am not aware of any such free utility or DB feature. But there could be some third party tools available for this.

Thanks Sarab,

That is my impression too. I am still looking for best solution of comparing two tables having more that 70 columns. Can I use checksum and binary checksum both and then compare? I though if I use these two computed columns(persisted and then create index on it) and then compare on the basis of both columns.

I will appriciate if you can give any clue/support.

Thanks,

Naveen

Hi Naveen - You can either use 

- select CONVERT(BIGINT, CONVERT(BINARY(4), CHECKSUM(REVERSE('checksum'))) + CONVERT(BINARY(4), CHECKSUM('checksum')))

Or 

- hasbytes

Thanks,

Ahmad

You can use SSDT now, it has a feature called Data Compare :)