CheckSum_Agg function returns 0 for even number of repeated values?

• Eric M Russell

  SSC Guru

  Points: 125620

  More actions

  August 14, 2014 at 8:57 am

  #397202

  From what I've seen, the CheckSum_Agg function appears to returns 0 for even number of repeated values. If so, then what is the practical use of this function for implementing an aggregate checksum across a set of values?

  For example, the following work as expected; it returns a non-zero checksum across (1) value or across (2) unequal values.

  declare @t table ( ID int );

  insert into @t ( ID ) values (-7077);

  select checksum_agg( ID ) from @t;

  -----------

  -7077

  declare @t table ( ID int );

  insert into @t ( ID ) values (-7077), (-8112);

  select checksum_agg( ID ) from @t;

  -----------

  1035

  However, the function appears to returns 0 for an even number of repeated values.

  declare @t table ( ID int );

  insert into @t ( ID ) values (-7077), (-7077);

  select checksum_agg( ID ) from @t;

  -----------

  0

  It's not specific to -7077, for example:

  declare @t table ( ID int );

  insert into @t ( ID ) values (-997777), (-997777);

  select checksum_agg( ID ) from @t;

  -----------

  0

  What's curious is that (3) repeated equal values will return a checksum > 0.

  declare @t table ( ID int );

  insert into @t ( ID ) values (-997777), (-997777), (-997777);

  select checksum_agg( ID ) from @t;

  -----------

  -997777

  But a set of (4) repeated equal values will return 0 again.

  declare @t table ( ID int );

  insert into @t ( ID ) values (-997777), (-997777), (-997777), (-997777);

  select checksum_agg( ID ) from @t;

  -----------

  0

  Finally, a set of (2) uneuqal values repeated twice will return 0 again.

  declare @t table ( ID int );

  insert into @t ( ID ) values (-997777), (8112), (-997777), (8112);

  select checksum_agg( ID ) from @t;

  -----------

  0

  "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho