SQL Server - Behaviour of ROW_NUMBER Partition by Null Value

Question

I find this behaviour very strange and counterintuitive. (Even for SQL).

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

Which returns

1   3   NULL
2   4   NULL
3   5   NULL
4   6   NULL
1   1   1
1   2   2

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

  select 1, '1'
  union 
  select 2, '2'
  union 
  select 5, null
  union 
  select 6, null
  union
  select 3, null
  union 
  select 4, null

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?

The results I would have expected would have been

1   3   NULL
1   4   NULL
1   5   NULL
1   6   NULL
1   1   1
1   2   2

Using Dense_Rank() makes no difference.

Guinna

PARTITION BY builds groups, so isn't it consistent? GROUP BY does the same which is documented in the SQL:2003 standard. Read – Phenyl

Read it. But a group does not have an inherent order within the group - partition by does create such an order and as such is fundamentally different – Guinna

Answer 1

Yo.

So the deal is that when T-SQL is dealing with NULLs in predicates, it uses ternary logic (TRUE, FALSE or UNKNOWN) and displays the behavior that you have stated that you expect from your query. However, when it comes to grouping values, T-SQL treats NULLs as one group. So your query will group the NULLs together and start numbering the rows within that window.

For the results that you say you would like to see, this query should work...

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
    DENSE_RANK() OVER (PARTITION BY CanBeNull ORDER BY CASE WHEN CanBeNull IS NOT NULL THEN value END ASC) as RowNumber
    ,Value
    ,CanBeNull
FROM sampledata

Variscite