Why a NOT NULL Computed Column is Considered Nullable in a View

computed-columnnullsql serverview

I have a table:

CREATE TABLE [dbo].[Realty](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [RankingBonus] [int] NOT NULL,
    [Ranking]  AS ([Id]+[RankingBonus]) PERSISTED NOT NULL
    ....
)

And a view:

CREATE View  [dbo].[FilteredRealty] AS
 SELECT 
realty.Id as realtyId,
...
COALESCE(realty.Wgs84X, ruian_cobce.Wgs84X, ruian_obec.Wgs84X) as Wgs84X,
COALESCE(realty.Wgs84Y, ruian_cobce.Wgs84Y, ruian_obec.Wgs84Y) as Wgs84Y,
realty.Ranking,
...
FROM realty
JOIN Category ON realty.CategoryId = Category.Id
LEFT JOIN ruian_cobce ON realty.cobceId = ruian_cobce.cobce_kod
LEFT JOIN ruian_obec ON realty.obecId = ruian_obec.obec_kod
LEFT JOIN okres ON realty.okresId = okres.okres_kod
LEFT JOIN ExternFile ON realty.Id = ExternFile.ForeignId AND ExternFile.IsMain = 1
                     AND ExternFile.ForeignTable = 5
INNER JOIN Person ON realty.OwnerId = Person.Id
WHERE Person.ConfirmStatus = 1

I have a dbml model in C# (LinqToSQL) with the FilteredRealty view in it. The [Ranking] field is recognized as a nullable int and so I have to fix the type in the generated code every time when I change anything in the database. This is very frustrating for me and a lot of manual work.

There are no aggregates used in FilteredRealty (regarding this related question).

Why is the Ranking column of the view considered as nullable if Realty.Ranking is non-nullable?

Best Answer

The [Ranking] field is showing as "Nullable" due to being a computed column. Yes, it is declared as NOT NULL, but as the MSDN page for Computed Columns states, the database engine can change that determination at query-time:

The Database Engine automatically determines the nullability of computed columns based on the expressions used. The result of most expressions is considered nullable even if only nonnullable columns are present, because possible underflows or overflows will produce null results as well. Use the COLUMNPROPERTY function with the AllowsNull property to investigate the nullability of any computed column in a table. An expression that is nullable can be turned into a nonnullable one by specifying ISNULL(check_expression, constant), where the constant is a nonnull value substituted for any null result.

So, let's see if this is true:

CREATE TABLE [dbo].[Realty](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [RankingBonus] [int] NOT NULL,
    [Ranking]  AS ([Id]+[RankingBonus]) PERSISTED NOT NULL
);
GO

EXEC sp_help 'dbo.Realty';
-- Ranking: Nullable = "no"

SELECT COLUMNPROPERTY(OBJECT_ID(N'dbo.Realty'), N'Ranking', 'AllowsNull') AS [AllowsNull?];
-- 0

SELECT * FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.Realty', '', NULL);
-- Ranking: is_nullable = 1  ==  :-(

Now let's see if their advice regarding ISNULL works:

SELECT * FROM sys.dm_exec_describe_first_result_set(
   N'SELECT Id, RankingBonus, ISNULL(Ranking, -99) AS [RealRanking] FROM dbo.Realty;',
   '',
   NULL);
-- RealRanking: is_nullable = 0

Their advice does seem accurate, so let's try applying that to the definition of the computed column:

ALTER TABLE dbo.Realty
  ADD [RankingFixed] AS (ISNULL(([Id]+[RankingBonus]), -99))
  PERSISTED NOT NULL;
GO

And now we check the properties again, but for the new field:

EXEC sp_help 'dbo.Realty';
-- RankingFixed: Nullable = "no"

SELECT COLUMNPROPERTY(OBJECT_ID(N'dbo.Realty'),
                      N'RankingFixed',
                      'AllowsNull') AS [AllowsNullsNow?];
-- 0

This is looking positive so far, but even the original definition reported "NOT NULL" from these two checks. So let's try the real test -- how the database engine determines the nullability at run-time:

SELECT * FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.Realty', '', NULL);
-- RankingFixed: is_nullable = 0  ==  :-) WOO HOO!
Related Question