Really poor checksum (Checksum_agg())
-
The BOL suggests using CHecksum_agg(Binary_Checksum(*)) as a way to see if a table has changed.
It really seems to work pretty poorly in the first test I ran of it. Below is what I tried, and as you can see everytime I add a new (identical) row it alternates between two values. I then tried a different row value -- did get a new checksum, but inserted two of those and got it alternating again.
I tried a few other variations, and on large tables with many columns I had better luck, but this first try really made me wonder of the quality of this technique.
What am I missing? Is there a better way?
Try running the below watch the values.
set nocount on
create table a (x int, y int)
insert into a (x,y) values (1,2)
select Checksum_agg(Binary_Checksum(*)) from a
insert into a (x,y) values (1,2)
select Checksum_agg(Binary_Checksum(*)) from a
insert into a (x,y) values (1,2)
select Checksum_agg(Binary_Checksum(*)) from a
insert into a (x,y) values (1,2)
select Checksum_agg(Binary_Checksum(*)) from a
insert into a (x,y) values (1,5)
select Checksum_agg(Binary_Checksum(*)) from a
insert into a (x,y) values (1,5)
select Checksum_agg(Binary_Checksum(*)) from a
insert into a (x,y) values (1,5)
select Checksum_agg(Binary_Checksum(*)) from a
drop table a
-
This was removed by the editor as SPAM
-
I'd say those results point to the Binary_Checksum algorithm relying heavily on XOR functions. Any odd number of identical records produces an identical checksum, and any even number of identical rows produces an identical checksum.
Are you storing the checksum results in another table, so that you can periodically check to see which tables have changed? If so, maybe you could store a Decimal instead of an Integer, and append the COUNT(*) value as the fractional portion of your checksum.
-
Yes, I am, and yes I could, and probably will.
I'm just disappointed that the tool designed just for this purpose is so poor. It's like no one ever learned anything from all the other checksum and related algorithms used for all these years.
-
It's almost humorous (to everyone except you, probably
) that such a simple scenario breaks their algorithm! I bet it was never in their test cases. Have you thought of anything faster than (but just as reliable as) COUNT(*) to track a difference?
) that such a simple scenario breaks their algorithm! I bet it was never in their test cases. Have you thought of anything faster than (but just as reliable as) COUNT(*) to track a difference?Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply