Why do T-SQL ranking values return nullable columns?

Viewed 305

When using the ranking functions of T-SQL, the column containing ranking values is nullable.

You can see this when creating a view of the result set:

CREATE VIEW v
AS 
  SELECT Name
    , ListPrice
    , RANK() OVER (ORDER BY ListPrice DESC) AS [Rank]
    , DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS [DenseRank]
    , ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS [RowNumber]
FROM Production.Product

Executing sp_help for this view indicates that the columns using ranking functions are nullable:

EXEC sp_help 'v'

Column_name   (...) | Nullable
---------------...-+------------+
...           (...) | ...
Rank          (...) | Yes
DenseRank     (...) | Yes
RowNumber     (...) | Yes

Which condition would cause a ranking function to return NULL?

1 Answers
Related