Using BINARY_CHECKSUM on multiple tables with join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Using BINARY_CHECKSUM on multiple tables with join

Author

Topic

alexjamesbrown
Starting Member

48 Posts

Posted - 2012-01-27 : 16:35:10

I'm trying to do a very hacky change tracking excercise using a legacy application.

What I'm planning to do, is store the BINARY_CHECKSUM values of each row in a separate table, to compare against in my app.

Imagine I have 2 tables - TableA, TableB
Both have a ProductID column (So can join the two tables together)

I'm looking for a query something like:

SELECT a.ProductID a.brand, a.BINARY_CHECKSUM(*)AS Hash1, b.BINARY_CHECKSUM(*) AS Hash2 FROM
TableA a
JOIN
TableB b
ON a.ProductID = b.ProductID

This obviously doesn't work... but something along those lines, so the result would be (for example)

ProductID | Brand | Hash1 | Hash2
1234 | XYZ | --439419708 | -35860977
1234 | ABC | --439419708 | -35860977

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-01-27 : 18:41:45

[CODE]SELECT a.ProductID, a.brand, a.Hash Hash1, b.Hash Hash2
FROM (
select ProductID, brand, BINARY_CHECKSUM(*) AS Hash
from TableA
) a
JOIN
TableB b
ON a.ProductID = b.ProductID[/CODE]HTH

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

alexjamesbrown
Starting Member

48 Posts

Posted - 2012-01-30 : 06:51:43

Sorry-
Gives Invalid column name 'Hash'.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-30 : 07:02:58

Bustaz probably meant to put TableB also in a subquery, like this:

SELECT
	a.ProductID,
	a.brand,
	a.Hash Hash1,
	b.Hash Hash2
FROM
	(
	    SELECT
	    	ProductID,
	    	brand,
	    	BINARY_CHECKSUM(*) AS HASH
	    FROM
	    	TableA
	) a
	JOIN
	(
	    SELECT
	    	ProductID,
	    	BINARY_CHECKSUM(*) AS HASH
	    FROM
	    	TableB
	) b
	ON  a.ProductID = b.ProductID

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-01-30 : 14:23:44

just be aware that

BOL


 BINARY_CHECKSUM(*) will return a different value for most, but not all, changes to the row, and can be used to detect most row modifications.

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources