CODEWITHSUNDEEP
sql-servert-sql
vstrien
vstrien

Reputation: 2605

Why do T-SQL ranking values return nullable columns?

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?

Upvotes: 9

Views: 328

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

Every computed/function based column in a view appears to be nullable. E.g.:

create view v1
as
    select OBJECT_ID,OBJECT_ID * 1 as obj2 from sys.objects
go
EXEC sp_help 'v1'

Indicates that object_id is not nullable but that obj2 is, even though it's trivially observable that if object_id can never be null, nor can obj2.

The only way I know of (not sure if this is what you're really looking for) to force a column to appear to not be nullable, is to wrap it in an ISNULL:

create view v2
as
    select OBJECT_ID,ISNULL(OBJECT_ID * 1,0) as obj2 from sys.objects
go
EXEC sp_help 'v2'

Interestingly, this is one of the few places where you can't use COALESCE instead of ISNULL:

create view v3
as
    select OBJECT_ID,COALESCE(OBJECT_ID * 1,0) as obj2 from sys.objects
go
EXEC sp_help 'v3'

v3 resembles v1.

Upvotes: 8

Related Questions