title: BINARY_CHECKSUM - different result depending on number of rows
body: <p>I wonder why the <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/binary-checksum-transact-sql?view=sql-server-2017" rel="noreferrer"><code>BINARY_CHECKSUM</code></a> function returns different result for the same:</p>
  
  <pre><code>SELECT *, BINARY_CHECKSUM(a,b) AS bc
  FROM (VALUES(1, NULL, 100),
              (2, NULL, NULL),
              (3, 1, 2)) s(id,a,b);
  
  SELECT *, BINARY_CHECKSUM(a,b) AS bc
  FROM (VALUES(1, NULL, 100),
              (2, NULL, NULL)) s(id,a,b);
  </code></pre>
  
  <p>Ouput:</p>
  
  <pre><code>+-----+----+------+-------------+
  | id  | a  |  b   |     bc      |
  +-----+----+------+-------------+
  |  1  |    | 100  |        -109 |
  |  2  |    |      | -2147483640 |
  |  3  | 1  |   2  |          18 |
  +-----+----+------+-------------+
  
  -- -109 vs 100
  +-----+----+------+------------+
  | id  | a  |  b   |     bc     |
  +-----+----+------+------------+
  |  1  |    | 100  |        100 |
  |  2  |    |      | 2147483647 |
  +-----+----+------+------------+
  </code></pre>
  
  <hr>
  
  <p>And for second sample I get what I would anticipate:</p>
  
  <pre><code>SELECT *, BINARY_CHECKSUM(a,b) AS bc
  FROM (VALUES(1, 1, 100),
              (2, 3, 4),
              (3,1,1)) s(id,a,b);
  
  SELECT *, BINARY_CHECKSUM(a,b) AS bc
  FROM (VALUES(1, 1, 100),
              (2, 3, 4)) s(id,a,b);
  </code></pre>
  
  <p>Ouptut for both first two rows:</p>
  
  <pre><code>+-----+----+------+-----+
  | id  | a  |  b   | bc  |
  +-----+----+------+-----+
  |  1  | 1  | 100  | 116 |
  |  2  | 3  |   4  |  52 |
  +-----+----+------+-----+
  </code></pre>
  
  <p><strong><a href="https://dbfiddle.uk/?rdbms=sqlserver_2017&amp;fiddle=7aeb37bb808cdc3e06a08c9298eaf9e5" rel="noreferrer">db&lt;>fiddle demo</a></strong></p>
  
  <hr>
  
  <p>It has strange consequences when I want to compare two tables/queries:</p>
  
  <pre><code>WITH t AS (
    SELECT 1 AS id, NULL AS a, 100 b
    UNION ALL SELECT 2, NULL, NULL
    UNION ALL SELECT 3, 1, 2           -- comment this out
  ), s AS (
    SELECT 1 AS id ,100 AS a, NULL as b
    UNION ALL SELECT 2, NULL, NULL
    UNION ALL SELECT 3, 2, 1           -- comment this out
  )
  SELECT t.*,s.*
    ,BINARY_CHECKSUM(t.a, t.b) AS bc_t, BINARY_CHECKSUM(s.a, s.b) AS bc_s
  FROM t
  JOIN s
    ON s.id = t.id
  WHERE BINARY_CHECKSUM(t.a, t.b) = BINARY_CHECKSUM(s.a, s.b);
  </code></pre>
  
  <p><strong><a href="https://dbfiddle.uk/?rdbms=sqlserver_2017&amp;fiddle=bdcddf454d7e88511cc2d48a06a9dd6e" rel="noreferrer">db&lt;>fiddle demo2</a></strong></p>
  
  <p>For 3 rows I get single result:</p>
  
  <pre><code>+-----+----+----+-----+----+----+--------------+-------------+
  | id  | a  | b  | id  | a  | b  |    bc_t      |    bc_s     |
  +-----+----+----+-----+----+----+--------------+-------------+
  |  2  |    |    |  2  |    |    | -2147483640  | -2147483640 |
  +-----+----+----+-----+----+----+--------------+-------------+
  </code></pre>
  
  <p>but for 2 rows I get also id = 1:</p>
  
  <pre><code>+-----+----+------+-----+------+----+-------------+------------+
  | id  | a  |  b   | id  |  a   | b  |    bc_t     |    bc_s    |
  +-----+----+------+-----+------+----+-------------+------------+
  |  1  |    | 100  |  1  | 100  |    |        100  |        100 |
  |  2  |    |      |  2  |      |    | 2147483647  | 2147483647 |
  +-----+----+------+-----+------+----+-------------+------------+
  </code></pre>
  
  <p>Remarks:</p>
  
  <ul>
  <li><p>I am not searching for alternatives like(HASH_BYTES/MD5/CHECKSUM)</p></li>
  <li><p>I am aware that <code>BINARY_CHECKSUM</code> could lead to collisions(two different calls produce the same output) here scenario is a bit different</p></li>
  </ul>
  
  <blockquote>
    <p>For this definition, we say that null values, of a specified type,
    compare as equal values. If at least one of the values in the
    expression list changes, the expression checksum can also change.
    However, this is not guaranteed. Therefore, to detect whether values
    have changed, we recommend use of BINARY_CHECKSUM only if your
    application can tolerate an occasional missed change.</p>
  </blockquote>
  
  <p>It is strange for me that <strong>hash function returns different result for the same input arguments</strong>.
  Is this behaviour by design or it is some kind of glitch?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>As <a href="https://stackoverflow.com/users/6167855/scsimon">@scsimon</a>
   points out it works for materialized tables but not for cte. 
  <strong><a href="https://dbfiddle.uk/?rdbms=sqlserver_2017&amp;fiddle=9e53d7e9d72cda6e2cb11878c3be9a2e" rel="noreferrer">db&lt;>fiddle actual table</a></strong></p>
  
  <p>Metadata for cte:</p>
  
  <pre><code>SELECT name, system_type_name
  FROM sys.dm_exec_describe_first_result_set('
  SELECT *
  FROM (VALUES(1, NULL, 100),
              (2, NULL, NULL),
              (3, 1, 2)) s(id,a,b)', NULL,0);
  
  SELECT name, system_type_name
  FROM sys.dm_exec_describe_first_result_set('
  SELECT *
  FROM (VALUES(1, NULL, 100),
              (2, NULL, NULL)) s(id,a,b)', NULL,0)
  
  -- working workaround
  SELECT name, system_type_name
  FROM sys.dm_exec_describe_first_result_set('
  SELECT *
  FROM (VALUES(1, cast(NULL as int), 100),
              (2, NULL, NULL)) s(id,a,b)', NULL,0)
  </code></pre>
  
  <p>For all cases all columns are <code>INT</code> but with explicit <code>CAST</code> it behaves as it should.</p>
  
  <p><strong><a href="https://dbfiddle.uk/?rdbms=sqlserver_2017&amp;fiddle=916abf7f96fb7f36421a31846bd50dd1" rel="noreferrer">db&lt;>fidde metadata</a></strong></p>