Using BINARY_CHECKSUM(*) with multiple tables

问题:

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.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 | Hash1 | Hash2
1234 | --439419708 | -35860977

回答1:

This might work:

SELECT a.ProductID, Hash1, Hash2
FROM (select ProductID, BINARY_CHECKSUM(*) AS Hash1 from TableA a) a
JOIN (select ProductID, BINARY_CHECKSUM(*) AS Hash1 from TableB b) b on ...

Your problem is just a syntax problem. Your approach will work.

回答2:

you might something try this:

with a as (
select BINARY_CHECKSUM(*) as CheckSum,
*
from TableA
),
b as (
select BINARY_CHECKSUM(*) as CheckSum,
*
from TableB
)
select 
a.keyField, a.CheckSum, b.CheckSum
from 
a
full outer join
b
on a.keyField = b.keyField
where a.CheckSum <> b.CheckSum