Checksum Computation Help

Please execute the script below to understand the problem -


---
create table test(id int, col1 int,col2 varchar(5),col3 datetime)
create table test2(id int, col1 int,col2 varchar(5),col3 datetime)

--id & col1 make up the PK.

insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'e','02/06/2004')

insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'e','02/06/2004')

select *
from test

select *
from test2

--The rows are identical.
--Script A

select t.*
from test t
join test2 t2 on t2.id=t.id
where CHECKSUM(t.col2,t.col3)<>CHECKSUM(t2.col2,t2.col3)

--The purpose of the above script is to check for any updates in the two tables. It returns two rows. But as you can see both these rows were present in the table before. So I modify the script to -
--SCRIPT B
select t.*
from test t
join test2 t2 on t2.col2=t.col2
where CHECKSUM(t.col3)<>CHECKSUM(t2.col3)

-- In this case no row is returned.This is exactly what I need. The problem - Now execute the script below.

TRUNCATE TABLE TEST
TRUNCATE TABLE TEST2

insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'d','02/01/2004')

insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'d','02/01/2004')

--Now when I execute script B two rows are returned which is not what I want. Since the rows are identical no row should be returned. So depending on what column changes (col2 or col3), I have to alter the script. I seek advise on the method to calculate checksum. Again the PK is ID and Col1 only.


Thanks



drop table test
drop table test2
go
--