title: What conditions cause CHECKSUM_AGG to return 0?
body: <p>It seems that there are a number of conditions that cause <code>CHECKSUM_AGG</code> to return 0 that I wouldn't have expected. I've only been able to find one discussed, which is that duplicate values will cause it. That can be solved via a <code>DISTINCT</code> or <code>GROUP BY</code>.</p>
  
  <p>I've also found a couple more scenarios that make less sense to me. One was provided by my boss and one I found <a href="http://social.msdn.microsoft.com/Forums/en/transactsql/thread/3e0c17ac-fc6b-469d-9542-940b0752fdf4" rel="nofollow">MSDN</a>. These I don't know how to explain. Here is some SQL that demonstrates the scenarios:</p>
  
  <pre><code>SELECT
      CHECKSUM_AGG(T.Number) AS ChecksumAgggregate
  FROM
  (
      VALUES
          (2)
          , (3)
          , (4)
          , (5)
  )AS T(Number)
  
  DECLARE @t TABLE 
  (
      Category VARCHAR(15),
      Value VARCHAR(10)
  )
  
  INSERT @t 
  (
      Category
      , Value
  )
  VALUES 
      ('OneCharacter','a')
      ,('OneCharacter','b')
      ,('OneCharacter','c')
      ,('OneCharacter','d')
  
      ,('TwoCharacters','aa')
      ,('TwoCharacters','bb')
      ,('TwoCharacters','cc')
      ,('TwoCharacters','dd')
  
      ,('ThreeCharacters','aaa')
      ,('ThreeCharacters','bbb')
      ,('ThreeCharacters','ccc')
      ,('ThreeCharacters','ddd')
  
      ,('SixCharacters','aaaaaa')
      ,('SixCharacters','bbbbbb')
      ,('SixCharacters','cccccc')
      ,('SixCharacters','dddddd')
  
      ,('AllValues','a')
      ,('AllValues','b')
      ,('AllValues','c')
      ,('AllValues','d')
      ,('AllValues','aa')
      ,('AllValues','bb')
      ,('AllValues','cc')
      ,('AllValues','dd')
      ,('AllValues','aaa')
      ,('AllValues','bbb')
      ,('AllValues','ccc')
      ,('AllValues','ddd')
      ,('AllValues','aaaaaa')
      ,('AllValues','bbbbbb')
      ,('AllValues','cccccc')
      ,('AllValues','dddddd')
  
  select 
      Category, CHECKSUM_AGG(CHECKSUM(Value)) 
  from @t 
  group by Category
  
  select Category, Value, CHECKSUM(Value) ValueChecksum
  from @t
  </code></pre>
  
  <p>It's nothing but 0's in these examples from <code>CHECKSUM_AGG</code> from these queries. The last query shows that none of the <code>CHECKSUM</code> values that are getting input into the <code>CHECKSUM_AGG</code> call are duplicated.</p>
  
  <p>I'm hoping that whatever answer describes what causes <code>CHECKSUM_AGG</code> to return 0 will explain these situations as well.</p>