SQL Server - Behaviour of ROW_NUMBER Partition by Null Value
<p>I find this behaviour very strange and counterintuitive.  (Even for SQL).</p>

<pre><code>set ansi_nulls off
go
;with sampledata(Value, CanBeNull) as
(
  select 1, 1
  union 
  select 2, 2
  union 
  select 3, null
  union 
  select 4, null
  union 
  select 5, null
  union 
  select 6, null
)
select ROW_NUMBER() over(partition by CanBeNull order by      value) 'RowNumber',* from sampledata
</code></pre>

<p>Which returns</p>

<pre><code>1   3   NULL
2   4   NULL
3   5   NULL
4   6   NULL
1   1   1
1   2   2
</code></pre>

<p>Which means that all of the nulls are being treated as part of the same group for the purpose of calculating the row number.  It doesn't matter whether the SET ANSI_NULLLS is on or off.
But since by definition the null is totally unknown then how can the nulls be grouped together like this?  It is saying that for the purposes of placing things in a rank order that apples and oranges and the square root of minus 1 and quantum black holes or whatever can be meaningfully ordered.  A little experimentation suggests that the first column is being used to generate the rank order as</p>

<pre><code>  select 1, '1'
  union 
  select 2, '2'
  union 
  select 5, null
  union 
  select 6, null
  union
  select 3, null
  union 
  select 4, null
</code></pre>

<p>generates the same values.  This has significant implications which have caused problems in legacy code I am dealing with.  Is this the expected behaviour and is there any way of mitigating it other than replacing the null in the select query with a unique value?</p>

<p>The results I would have expected would have been</p>

<pre><code>1   3   NULL
1   4   NULL
1   5   NULL
1   6   NULL
1   1   1
1   2   2
</code></pre>

<p>Using Dense_Rank() makes no difference.</p>