SQL View 从非空表推断可为空列？

Question

我有一个包含非空“数量”(十进制)和“状态”(整数)列的产品表，并使用以下 case 表达式在此表上创建了一个 View :

SELECT P.ProductTypeId,
       (CASE WHEN P.StatusId IN (5, 8) THEN 0 ELSE -P.Quantity END) AS Quantity,
       ...
FROM Product P

ProductTypeId 被正确推断为非空。但是，此 View 的 Quantity 列被推断为可为空，即使基础列不可为空。这对我来说没有任何意义。

在这种情况下，我可以使用 ISNULL/COALESCE 提供默认值并强制不可为空，但没有有意义的默认值，根据我的理解，这首先不应该发生。知道发生了什么吗？

Answer 1

以下解释适用于 computed columns在一个表中。我想这同样适用于 View 中的计算列。

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.

表达式可能返回 NULL 的示例是

SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;

WITH Product(Quantity,StatusId) As
(
SELECT -2147483648,1
)
SELECT (CASE WHEN P.StatusId IN (5, 8) THEN 0 ELSE -P.Quantity END) AS Quantity  
FROM Product P