Checksum_Agg - how does it work?

Please can somebody tell me how the CHECKSUM_AGG function works?

When I run the following query:

SELECT STUDENTID,
CHECKSUM_AGG(Grade) as GradeCheckSumAgg
FROM  (VALUES (1,100),
(1,100),
(1,100),
(1,99),
(1,99),
(1,98),
(1,98),
(1,95),
(1,95),
(1,95)
) dr (Studentid, Grade)
Group by Studentid

The value 59 is returned for the CheckSumAgg - can somebody tell me why 59 is returned?

September 14th, 2015 3:53pm

It runs an xor over the values. Convert all values to binary put them on top of each other, and count the number of bits in each column and at the end put 1, if there is an odd number of 1, else put a 0. Convert this result to decimal again, and you should have 59.

The checksum functions are not particularly good. The algorithm is too simple-minded and with 32-bit return space the chance for different datasets producing the same checksum is non-negligible.

Here is an example that is easier to play with:

SELECT Studentid,
CHECKSUM_AGG(Grade) as GradeCheckSumAgg
FROM  (VALUES (1,1),
(1,2),
(1,4),
(1,8),
(1,16),
(1,32),
(1,64),
(1,128),
(1,256),
--(1,1),
(1,512)
) dr (Studentid, Grade)
Group by Studentid

Free Windows Admin Tool Kit Click here and download it now
September 14th, 2015 5:37pm

Hi,

check this post regarding Bitwise. Everything is explains there with images and examples. While reading the post, you can focus on XOR (as Erland mantioned) since this is what checksum_agg function does :-)

http://ariely.info/Blog/tabid/83/EntryId/166/SQL-Server-Bitwise-Operators.aspx

* this link give you the basic explanation regarding checksum_agg  https://msdn.microsoft.com/en-us/library/ms188920.aspx?f=255&MSPPError=-2147217396 together with the the explanation about Bitwise you should have the full understanding :-)

September 14th, 2015 6:04pm